Forum Discussion

Shanks_403's avatar
Shanks_403
Frequent Visitor
3 years ago

Calculating Running Total based on slicer date range

Hi friends, I'm trying to calculate a running total based on the slicer selection for the date range. For some reason every time I put in the start date, the running total stops working. I think I'm...
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure how your calendar table is structured, but please try something like below whether it works.

 

 

Total Spent Running Total =
VAR _year =
    MAX ( CalendarDates[FiscalYear] )
VAR _fyadjust =
    MAX ( CalendarDates[FiscalYearAdjusted] )
VAR MinDate =
    CALCULATE ( MIN ( CalendearDates[Dates] ), ALLSELECTED ( CalendarDates ) )
VAR MaxDate =
    CALCULATE (
        MAX ( CalendarDates[Dates] ),
        FILTER (
            ALLSELECTED ( CalendarDates ),
            CalendarDates[Year] = _year
                && CalendarDates[FiscalYearAdjusted] = _fyadjust
        )
    )
VAR DatesToUse =
    FILTER (
        ALL ( CalendarDates ),
        CalendarDates[Dates] >= MinDate
            && CalendarDates[Dates] <= MaxDate
    )
VAR Result =
    CALCULATE ( [Total Spent], DatesToUse )
RETURN
    Result

 

Shanks_403's avatar
Shanks_403
Frequent Visitor
3 years ago

Hello, 

 

Thanks for your response. My calendar is automated in Power Query and it just adds a day every day on the CalendarDates[Dates] column in the format "yyyy-MM-dd". I revised the formula below but still no luck. Any thoughts?

Total Actuals to Date =
VAR MinDate = CALCULATE(MIN(CalendarDates[Dates]),ALLSELECTED(CalendarDates[Dates]))
VAR MaxDate = CALCULATE(MAX(CalendarDates[Dates]),ALLSELECTED(CalendarDates[Dates]))
VAR DatesToUse = FILTER (
        ALL(CalendarDates),
        CalendarDates[Dates] >= MinDate
            && CalendarDates[Dates] <= MaxDate
)
RETURN
    CALCULATE(
        [Sum of PaidTotal_Accruals],
        DatesToUse
    )