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

 

 

2 Replies

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