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('C...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    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 ) )
    )
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