Cumulative count for each level by month

[AlwaysAGooner]

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'.

[AlwaysAGooner]

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

[Anonymous]

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

[AlwaysAGooner]

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.