topic Re: Cumulative count for each level by month in DAX Commands and Tips

Cumulative count for each level by month

AlwaysAGooner — Wed, 03 Jan 2024 09:10:27 GMT

Hey wonderful ppl,

New Year Greetings.

Am in need to calculate cumulative count for each level by months. There are three levels in the data, namely 'A', 'B' and 'C', with 'A' being the lowest.

Need cumulative count for each of the aforementioned levels by month. Also, pls note that an ID can have two level during a month but must always be counted once and only for the Highest level achieved, for e.g. if an ID had level 'A' and level 'B' in January then this ID must only be counted for level 'B' and not for level 'A'.

Re: Cumulative count for each level by month

Anonymous — Thu, 04 Jan 2024 07:47:16 GMT

HI AlwaysAGooner,

You can try to use the following measure formula if helps:

formula = VAR cLevel = CALCULATETABLE ( VALUES ( Table1[ID] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Level] = "C" ), VALUES ( Table1[Date] ) ) VAR bLevel = CALCULATETABLE ( VALUES ( Table1[ID] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Level] = "B" && NOT ( [ID] IN cLevel ) ), VALUES ( Table1[Date] ) ) VAR aLevel = CALCULATETABLE ( VALUES ( Table1[ID] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Level] = "A" && NOT ( [ID] IN UNION ( cLevel, bLevel ) ) ), VALUES ( Table1[Date] ) ) VAR currLevel = SELECTEDVALUE ( Table1[Level] ) RETURN SWITCH ( currLevel, "A", COUNTROWS ( aLevel ), "B", COUNTROWS ( bLevel ), "C", COUNTROWS ( cLevel ) )

Regards,

Xiaoxin Sheng

Re: Cumulative count for each level by month

AlwaysAGooner — Thu, 04 Jan 2024 10:07:23 GMT

Thanks.

It doesn't give accurate results. For calculating cLevel, we cannot make sure that the id is not marked as A and B, hence there can be an overalp if there were any movers from cLevel to bLevel / aLevel during the time.
This means, anyone who once was at cLevel will be counted in cLevel. Hope this makes sense.

Re: Cumulative count for each level by month

AlwaysAGooner — Wed, 10 Jan 2024 05:49:33 GMT

Any more suggestions? Awaiting a working solution that gives the right result
Thanks