Forum Discussion

erikm's avatar
erikm
Frequent Visitor
5 years ago
Solved

Issue with displaying rolling totals

Hi,

I am trying to accomplish the following scenario:

  1. User selects one date (that is the end date of a selection)
  2. The beginning of the date range is automatically set to January 1st of the previous year
  3. The measure contains only +1 oder -1 values that need to be displayed in a graph as rolling totals.

In the workbook that I have uploaded here, I have prepared and drawn an example of how it should look like. I have tried several DAX statements to calculate the rolling total, but most of them either didn't show anything at all, or data from all years.

The dataset is based on the default WideWorldImporters database.

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi erikm 

    Try If function, I update the measure.

    balance RT =
VAR _EndtDay =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _StartDay =
    DATE ( SELECTEDVALUE ( 'Calendar'[YEAR] ) - 1, 01, 01 )
RETURN
    IF (
        MAX ( Movements[Year] ) * 100
            + MAX ( Movements[Month] )
            <= SELECTEDVALUE ( 'Calendar'[YEAR] ) * 100
                + SELECTEDVALUE ( 'Calendar'[Month] ),
        CALCULATE (
            SUM ( Movements[balance] ),
            FILTER (
                ALL ( Movements ),
                Movements[MeasureDate] >= _StartDay
                    && Movements[MeasureDate] <= _EndtDay
                    && Movements[Year] = MAX ( Movements[Year] )
                    && Movements[Month] <= MAX ( Movements[Month] )
            )
        ),
        BLANK ()
    )

    Result is as below.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    erikm , Assume you have selected a date in the slicer

     

    Try a measure like

     

    measure =
    Var _Min1 = MAXX(allselected('Date'), 'Date'[Date])
    Var _Min1 = date(year(min1)-1, 1,1)
    Var _Max = MAXX(allselected('Date'), 'Date'[Date])
    return CALCULATE(SUM(Table1[Value]), filter(Table1, Table1[date]=_max && Table1[date]=_min))
    // or
    return CALCULATE(SUM(Table1[Value]), filter(all(Date), Date[date]=_max && Date[date]=_min))

     

    • erikm's avatar
      erikm
      Frequent Visitor
      5 years ago

      Hi amitchandak ,

      thank you for your suggestion. It feels like I'm almost there 🙂 The graph is displayed correctly with the months filtered, the only thing is that it displayed the separate monthly values and not a rolling total. I've tried to create an extra measure based on this new one, but then all of a sudden all years are displayed again. Would you have a suggestion for this?
      Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi erikm 

        In dax, catching date values from automatic time intelligence hierachy will make your code logic complex and use date in same table to filter value will make result incorrect due to the impactions between columns in same table.

        Here I suggest you to turn off automatic time intelligence, and add an unrelated calendar table by dax.

         

        Calendar = ADDCOLUMNS(CALENDARAUTO(),"YEAR",YEAR([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"MonthName",FORMAT([Date],"MMMM"))

         

        Add Year, Month, MonthName columns into Movements Table by Dax.

         

        Year = YEAR(Movements[MeasureDate]) 
        Month = MONTH(Movements[MeasureDate])
        MonthName = FORMAT(Movements[MeasureDate],"MMM")

         

        Measure:

         

        balance RT = 
VAR _EndtDay = SELECTEDVALUE('Calendar'[Date])
VAR _StartDay = DATE(SELECTEDVALUE('Calendar'[YEAR])-1,01,01)
RETURN
CALCULATE(SUM(Movements[balance]),FILTER(ALL(Movements),Movements[MeasureDate]>=_StartDay&&Movements[MeasureDate]<=_EndtDay&&Movements[Year] = MAX(Movements[Year])&&Movements[Month]<=MAX(Movements[Month])))

         

        Result is as below.

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.