topic Re: Calculating Running Total based on slicer date range in DAX Commands and Tips

Calculating Running Total based on slicer date range

Shanks_403 — Sun, 30 Apr 2023 00:14:36 GMT

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 really close with the DAX code below but I can't figure out what I'm doing wrong. Any help would be greatly appreciated! Thanks in advance.

Total Spent Running Total =
VAR MinDate = CALCULATE(MIN(CalendearDates[Dates]),ALLSELECTED(CalendarDates[Dates]))
VAR MaxDate = CALCULATE(MAX(CalendarDates[Dates]),ALLSELECTED(CalendarDates[Dates]))
VAR DatesToUse =
DATESBETWEEN(
CalendarDates[Dates],
MinDate,
MaxDate)
VAR Result = CALCULATE([Total Spent],DatesToUse)
RETURN Result

Re: Calculating Running Total based on slicer date range

Jihwan_Kim — Sun, 30 Apr 2023 05:34:18 GMT

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

Re: Calculating Running Total based on slicer date range

Shanks_403 — Mon, 01 May 2023 15:48:54 GMT

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

)