DAX/M count for consecutive months for every ID in PowerBI

Question

I need help finding a way to count the number of consecutive months:

For every "ID" (Text Column)
Having the column "Status" either "Missing" or "On hold"

Here is what my table looks like (the last two columns are the output I would like to see):

ID	Year	Month	Date (01-Month-Year)	Status	Consecutive Months "Missing"	Consecutive Months "On Hold"
ID40	2019	6	01/06/2019	Missing	0	-
ID40	2019	7	01/07/2019	Missing	2	-
ID40	2019	8	01/08/2019	Missing	3	-
ID40	2019	11	01/11/2019	Missing	0	-
ID40	2019	12	01/12/2019	Missing	2	-
ID40	2020	9	01/09/2020	Missing	0	-
ID499	2019	1	01/01/2019	On Hold	-	0
ID499	2019	2	01/02/2019	On Hold	-	2
ID499	2019	3	01/03/2019	On Hold	-	3
ID499	2020	9	01/09/2020	On Hold	-	0
ID499	2020	10	01/10/2020	On Hold	-	2
ID499	2020	8	01/08/2020	Missing	0	-
ID499	2020	9	01/09/2020	Missing	2	-
ID499	2020	10	01/10/2020	Missing	3	-
ID499	2020	11	01/11/2020	Missing	4	-
ID499	2020	12	01/12/2020	Missing	5	-

Is there any way to do this besides with merged nested queries in "M"? Unfortunately I have already tried this, but PowerBI has trouble processing the data.

Thanks everyone in advance!

No, I don't think so. At the moment data is ordered first by Date and then by ID (95690 unique IDs). — DatAlessia
Data can be both Missing and OnHold at the same time, like for ID 499 for Month 10 Year 2020? — horseyride
You're right, my dummy data is not entirely correct; as you point out, for the same ID and in a given month, it is either Missing OR On Hold. — DatAlessia

sergiom sergiom · Accepted Answer · 2021-03-19T16:44:22

A possible solution is to build a table of the Years and Months since the first Year in the table and remove from it the Year Months that appear in the table. Than compute the difference in months between the current Year Month and the last non existent Year Month.

This is a possible calculated column that uses the EXCEPT function to build the table with the missing Year Months. To ease the computation of the difference in months across the years, it also prepares a YearMonthNumber value with the progressive month number from the first year month

Since in the example table there are separate columns for the Status, I added an IF to split the columns accordingly

I also added a check to return 0 instead of 1, to match the example data

Consecutive Months Missing = 
IF (
    T[Status] = "Missing",
    VAR FirstYear =
        MIN ( T[Year] )
    VAR CurrentYear = T[Year]
    VAR CurrentMonth = T[Month]
    VAR CurrentYearMonthNumber = ( CurrentYear - FirstYear ) * 12 + CurrentMonth
    VAR YearMonths =
        GENERATE (
            SELECTCOLUMNS ( GENERATESERIES ( FirstYear, CurrentYear ), "Year", [Value] ),
            SELECTCOLUMNS (
                GENERATESERIES ( 1, 12, 1 ),
                "Month", [Value],
                "YearMonthNumber",
                     ( [Year] - FirstYear ) * 12 + [Value]
            )
        )
    VAR CurrentIDAndStatusYearMonths =
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE ( T, T[Year], T[Month] ),
                "YearMonthNumber",
                     ( T[Year] - FirstYear ) * 12 + T[Month]
            ),
            ALLEXCEPT ( T, T[ID], T[Status] )
        )
    VAR MissingYearMonths =
        EXCEPT ( YearMonths, CurrentIDAndStatusYearMonths )
    VAR FirstMissingYearMonthNumber =
        MAXX (
            FILTER ( MissingYearMonths, [YearMonthNumber] < CurrentYearMonthNumber ),
            [YearMonthNumber]
        )
    VAR Result = CurrentYearMonthNumber - FirstMissingYearMonthNumber
    RETURN
        IF ( Result = 1, 0, Result )
)

and

Consecutive Months On Hold = 
IF (
    T[Status] = "On Hold",
    VAR FirstYear =
        MIN ( T[Year] )
    VAR CurrentYear = T[Year]
    VAR CurrentMonth = T[Month]
    VAR CurrentYearMonthNumber = ( CurrentYear - FirstYear ) * 12 + CurrentMonth
    VAR YearMonths =
        GENERATE (
            SELECTCOLUMNS ( GENERATESERIES ( FirstYear, CurrentYear ), "Year", [Value] ),
            SELECTCOLUMNS (
                GENERATESERIES ( 1, 12, 1 ),
                "Month", [Value],
                "YearMonthNumber",
                     ( [Year] - FirstYear ) * 12 + [Value]
            )
        )
    VAR CurrentIDAndStatusYearMonths =
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE ( T, T[Year], T[Month] ),
                "YearMonthNumber",
                     ( T[Year] - FirstYear ) * 12 + T[Month]
            ),
            ALLEXCEPT ( T, T[ID], T[Status] )
        )
    VAR MissingYearMonths =
        EXCEPT ( YearMonths, CurrentIDAndStatusYearMonths )
    VAR FirstMissingYearMonthNumber =
        MAXX (
            FILTER ( MissingYearMonths, [YearMonthNumber] < CurrentYearMonthNumber ),
            [YearMonthNumber]
        )
    VAR Result = CurrentYearMonthNumber - FirstMissingYearMonthNumber
    RETURN
        IF ( Result = 1, 0, Result )
)

this is the resulting table put in a table visual

DAX/M count for consecutive months for every ID in PowerBI

1 Answers