Forum Discussion

VladyOselsky's avatar
VladyOselsky
Frequent Visitor
3 years ago
Solved

Multiple Running Totals to Compare Different Years

Hello,   I'm trying to create comparison of 3 years worths of data based on most recent completed month. I created 3 measures [Sales $],[Sales $ LY] and [Sales $ LLY] that calculate Sales Total for...
  • VladyOselsky's avatar
    3 years ago

    I ended up solving it myself. I lost the count of how many variations of the DAX I tried until I came up with the following solution. 

    For starters I modified my calendar table to create groups of years. I added column Rolling12Months that defines each year

     

     

    Next I updated my [Sales $ LY] and [Sales $ LLY] to use Rolling12Month instead of MonthOrdinal, 
    Finally for Rolling Total Measures all of them are the same except which Sales Total they call

     

    -- Sales Totals
    MEASURE [Sales $] = CALCULATE ( SUM ( SalesData[SaleLineTotal] ) )
    
    MEASURE [Sales $ LY] = 
    VAR RY = [MinRollingYear] + 1
    VAR Results =
        CALCULATE (
            [Sales $],
            REMOVEFILTERS ( 'Dim - Date' ),
            'Dim - Date'[Rolling12Month] = RY,
            VALUES ('Dim - Date'[MonthName])
        )
    RETURN
        Results
    
    MEASURE [Sales $ LLY] = 
    VAR RY = [MinRollingYear] + 2
    VAR Results =
        CALCULATE (
            [Sales $],
            REMOVEFILTERS ( 'Dim - Date' ),
            'Dim - Date'[Rolling12Month] = RY,
            VALUES ('Dim - Date'[MonthName])
        )
    RETURN
        Results
    
    -- Rolling Totals
    MEASURE [Sales $ RT] = 
    var MaxMonth = MAX('Dim - Date'[MonthOrdinal])
    var Result = 
        CALCULATE(
                [Sales $],
                'Dim - Date'[MonthOrdinal] <= MaxMonth,
                REMOVEFILTERS('Dim - Date'),
                VALUES('Dim - Date'[Rolling12Month])
        )
    RETURN Result
    
    MEASURE [Sales $ RT LY] = 
    var MaxMonth = MAX('Dim - Date'[MonthOrdinal])
    var Result = 
        CALCULATE(
                [Sales $ LY],
                'Dim - Date'[MonthOrdinal] <= MaxMonth,
                REMOVEFILTERS('Dim - Date'),
                VALUES('Dim - Date'[Rolling12Month])
        )
    RETURN Result
    
    MEASURE [Sales $ RT LLY] = 
    var MaxMonth = MAX('Dim - Date'[MonthOrdinal])
    var Result = 
        CALCULATE(
                [Sales $ LLY],
                'Dim - Date'[MonthOrdinal] <= MaxMonth,
                REMOVEFILTERS('Dim - Date'),
                VALUES('Dim - Date'[Rolling12Month])
        )
    RETURN Result
    

     

    Fo the final result I finally got by graph that I was looking for.