Forum Discussion

NAOS's avatar
NAOS
Advocate III
2 years ago
Solved

Cumulative count with AllSELECTED

Hi all,

 

I have a table of Records with two columns: "Status" and "Activation Date"

 

I use the following measure to return the cumulative count of records per "Activation Date":

Cumulative_count = CALCULATE ( COUNTROWS ( 'Records' ),
FILTER ( ALLSELECTED ( 'Records' ), 'Records'[Activation Date] <= MAX ( 'Records'[Activation Date] )
))

 

I use this measure in a stacked column chart and get the result below:

 

 

What I would like to do next is to add the column legend, so I can split it by "Status". The problem is (I think) that because of the presense of ALLSELECTED, the count is performed again for each "Status" present within each year. So, instead of getting the split of the cumulative count per "Status", I get the following:

 

 

Any suggestion on how to adjust the calculation so I can get the cumulative count of records, but still be able to split it by status in the visual?

Thank you in advance for your help.

 

NAOS

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    NAOS , Create a separate date table and join it with your date and use it

     

    Cumulative_count = CALCULATE ( COUNTROWS ( 'Records' ),
    FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] )
    ))

     

     

    Using window function

     

    Cumm Based on Date = CALCULATE(COUNTROWS ( 'Records' ), Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

     

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

    • NAOS's avatar
      NAOS
      Advocate III
      2 years ago

      Hi amitchandak,

       

      That makes sense and of course it works.

       

      Thank you very much for your help.

       

      NAOS