Cumulative Stack Area Chart

[yosemite]

Hello,

I'm trying to create a cumulative stack area chart showing cases that are opened, closed, and pending. I also need to show how many are positives and presumptive.

1) What is the correct DAX formula to filter Sheet1[Positive]="Yes"? 

_CumulativePositive =

CALCULATE( COUNT (Sheet1[Positive]),

FILTER( ALLSELECTED (Sheet1[Case_DateOpened]),

Sheet1[Case_DateOpened] <= MAX (Sheet1[Case_DateOpened]) )

)

2) What is the correct DAX formula to filter Sheet1[ClosedCase]="Yes"? 

_CumulativeClosedCase =

CALCULATE( COUNT (Sheet1[ClosedCase]),

FILTER( ALLSELECTED (Sheet1[Case_DateOpened]),

Sheet1[Case_DateOpened] <= MAX (Sheet1[Case_DateOpened]) )

)

3) I also need to create a Pending chart by subtracting ClosedCase from the number of Case_DateOpened. Sample data below.

Thanks in advance for help!

[Anonymous · Accepted solution]

Hi @yosemite ,

To filter Sheet1[Positive]="Yes" or to filter Sheet1[ClosedCase]="Yes", you could put the fields into visual-level filters, report- level filters or slicers. For example:

Filters on this visual:

The slicer:

Or create a measure like

_CumulativeClosedCase = 
CALCULATE( COUNT (Sheet1[ClosedCase]),
FILTER( ALLSELECTED (Sheet1),
Sheet1[Case_DateOpened] <= MAX (Sheet1[Case_DateOpened]) &&[Positive]="Yes"))

Does "NULL" in your date column mean blank or just text NULL?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.