Forum Discussion

Natsnebki's avatar
Natsnebki
Frequent Visitor
3 years ago
Solved

Running count with changing categories

Hi,   I try to get the following matrix out of this data: Data: Date CAT Status 22/01/01 a YES 22/01/01 c YES 22/01/03 b YES 22/01/05 c NO 22/01/08 c YES 22/01/10...
  • Natsnebki's avatar
    Natsnebki
    3 years ago

    I solved it by creating a table with all dates between min date and max date, merge this full outer, sort by category and date and fill down all nulls in Status. 

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
3 years ago

ALLEXCEPT does not work the way you think it does. Please refer to https://dax.guide/allexcept for details. 

 

Here's the code you'd most likely want:

// Base measure
# CATs = DISTINCTCOUNT( 'Table'[CAT] )

// Base measure - RT
# CATs RT =
var LastVisibleDate = MAX( Dates[Date] )
var Result =
    CALCULATE(
        // YOu add 0 to be able to see
        // also the rows which would
        // have BLANKs.
        [# CATs] + 0,
        Dates[Date] <= LastVisibleDate,
        REMOVEFILTERS( Dates )
    )
return
    Result

 

  • Natsnebki's avatar
    Natsnebki
    Frequent Visitor
    3 years ago

    Thank you for your suggestion. This measure adds 0's in my matrix instead of blanks, but the count is still the same, and not what i need. The fact that a cat has a changing status, is not counted correctly. I hope I'm clear