Forum Discussion

btalahmbong's avatar
btalahmbong
Advocate II
9 years ago
Solved

Cumulative Total using waterfall chart

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!

Need Help

4 Replies

    • btalahmbong's avatar
      btalahmbong
      Advocate II
      9 years ago

      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's avatar
        v-chuncz-msft
        Community Support
        9 years ago

        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 ) )
    )