Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Column counting multiple consecutive months for every ID

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:

IDYearMonthDate (01-Month-Year)StatusConsecutive Months "Missing"Consecutive Months "On Hold"
ID402019601/06/2019Missing0-
ID402019701/07/2019Missing2-
ID402019801/08/2019Missing3-
ID4020191101/11/2019Missing0-
ID4020191201/12/2019Missing2-
ID402020901/09/2020Missing0-
ID4992019101/01/2019On Hold-0
ID4992019201/02/2019On Hold-2
ID4992019301/03/2019On Hold-3
ID4992020901/09/2020On Hold-0
ID49920201001/10/2020On Hold-2
ID4992020801/08/2020Missing0-
ID4992020901/09/2020Missing2-
ID49920201001/10/2020Missing3-
ID49920201101/11/2020Missing4-
ID49920201201/12/2020Missing5-



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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    5 years ago

    Hi Anonymous 

    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 "-"

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    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 "-"