Column counting multiple consecutive months for every ID

[AlessiaN]

Hello everyone!

I need help finding a way (whether it's in M or DAX makes no difference) to count the number of consecutive months for:

• Every "ID" (Text Column)
• Having the “Status” either “Missing” or “On hold”

Here is my dummy data; 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	-

I followed the advice of another user and calculated the consecutive months via multiple query merge in M, but the file has become unmanageable and PowerBI is unable to load it.

Please help and thanks a lot to everyone in advance!

Alessia

[Zubair_Muhammad · Accepted solution]

Hi @AlessiaN 

Try this custom column using M for consecutive missing months

=let
mymonth=[Month],
 mylist=Table.SelectRows(#"Changed Type",
(d)=>
d[Month]<=[Month] and
d[Year]=[Year] and
d[Status]=[Status] and
d[ID]=[ID]
) [Month],
merilist=List.Sort(mylist, Order.Descending),

myresult=List.Accumulate( merilist,0,(state,current)=> 
if current = mymonth-List.PositionOf(merilist,current) then (state + 1) else (state + 0) ),
interimresult=if myresult=1 then 0 else myresult
in
if [Status]="Missing" then interimresult else "-"