Forum Discussion

AlwaysAGooner's avatar
AlwaysAGooner
Icon for Helper I rankHelper I
2 years ago

Cumulative count for each level by month

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

 

CUMULATIVE COUNT
DAX Measures

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      AlwaysAGooner
      Icon for Helper I rankHelper I
      2 years ago

      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.

  • AlwaysAGooner's avatar
    AlwaysAGooner
    Icon for Helper I rankHelper I
    2 years ago

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