Cumulative count with AllSELECTED

[NAOS]

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

[amitchandak · Accepted solution]

@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]

Hi @amitchandak,

That makes sense and of course it works.

Thank you very much for your help.

NAOS