Cumulative Total using waterfall chart

[btalahmbong]

Hi,

I have measure which calculates the total cases created by date and it works pretty well. Here is the code;

Total Number Of Cases = CALCULATE(COUNTROWS('Case') + 0, FILTER(ALLSELECTED('Case'[created on local date]), 'Case'[created on local date] <= MAX ( 'Case'[created on local date])))

The visual I'm using to display this measure is a waterfall chart.

I want to be able to show the cumulative total of cases for the last 30 days. The problem is that instead of summing up the cases before 30 days ago, the chart starts counting the total cases from 30 days ago and cumulates.
So for example; Instead of starting on June 20th with 600 cases created, my chart starts on June 20th with 20 cases. 

Is there around that would suit my requirements for this visual? 

Thanks!

[v-chuncz-msft · Accepted solution]

@btalahmbong,

Here is a similar case for your reference.

http://community.powerbi.com/t5/Desktop/How-to-create-measure-for-waterfall-chart-that-begins-with/m...

[v-chuncz-msft · Accepted solution]

@btalahmbong,

Here is a similar case for your reference.

http://community.powerbi.com/t5/Desktop/How-to-create-measure-for-waterfall-chart-that-begins-with/m...

[btalahmbong]

@v-chuncz-msft This will work with slicers.

I'm looking for a solution that wouldn't need a slicer preferably. I already have a 'Is last 30 Days' field in my calendar date table that will slice the day for the last 30 days.

Thanks

[v-chuncz-msft · Accepted solution]

@btalahmbong,

Drag 'Calendar'[Date] to Category and use measure below.

Measure =
VAR minDate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Is last 30 Days] = TRUE () )
    )
VAR maxDate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Is last 30 Days] = TRUE () )
    )
VAR d =
    MAX ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        d = minDate, COUNTROWS ( FILTER ( 'Case', 'Case'[created on local date] <= d ) ),
        d > minDate
            && d <= maxDate, COUNTROWS ( FILTER ( 'Case', 'Case'[created on local date] = d ) )
    )

[klabir]

Have a look at the Ultimate Waterfall Chart in Office Store and here as well:

https://dataviz.boutique/