Multiple Running Totals to Compare Different Years

[VladyOselsky]

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 current, previous and 2 years ago. Everything is working properly, now I'm trying to create a running total for each of those periods and that is where I'm running into a problem. I created measures [Sales $ RT] that calculates running total based on [Saled $], but I can't figure out how to get running totals for [Sales $ LY] and [Sales $ LLY].

Here is code for all my measures.

-- [Sales $]
MEASURE [Sales $] = CALCULATE ( SUM ( SalesData[SaleLineTotal] ) )


-- [Sales $ RT]
MEASURE [Sales $ RT] = 
var MaxMonth = MAX('Dim - Date'[MonthOrdinal])
var Result = 
    CALCULATE(
            [Sales $],
            'Dim - Date'[MonthOrdinal] <= MaxMonth,
            REMOVEFILTERS('Dim - Date')
    )
RETURN Result

-- [Sales $ LY]
MEASURE [Sales $ LY] = 
VAR MaxMonth = [MaxMonthOrdinal] - 12
VAR Results =
    CALCULATE (
        [Sales $],
        REMOVEFILTERS ( 'Dim - Date' ),
        'Dim - Date'[MonthOrdinal] = MaxMonth
    )
RETURN
    Results
    
-- [Sales $ LLY]
MEASURE [Sales $ LLY] = 
VAR MaxMonth = [MaxMonthOrdinal] - 24
VAR Results =
    CALCULATE (
        [Sales $],
        REMOVEFILTERS ( 'Dim - Date' ),
        'Dim - Date'[MonthOrdinal] = MaxMonth
    )
RETURN
    Results

When I put data in the table I see correct data for [Sales $], [Sales $ LY] and [Sales $ RT] 

After that I switch to the line chart, what I need chart to show is 3 running totals instead of just 1.

Thanks,

[VladyOselsky · Accepted solution]

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.

[ADPowerBI1]

-- [Sales $ RT]
MEASURE [Sales $ RT] =
var MaxMonth = MAX('Dim - Date'[MonthOrdinal])
var Result =
CALCULATE(
[Sales $],
'Dim - Date'[MonthOrdinal] <= MaxMonth,
REMOVEFILTERS('Dim - Date')
)
RETURN Result

-- [Sales $ LY RT]
MEASURE [Sales $ LY RT] =
VAR MaxMonth = MAX('Dim - Date'[MonthOrdinal]) - 12
VAR Result =
CALCULATE(
[Sales $ LY],
'Dim - Date'[MonthOrdinal] <= MaxMonth,
REMOVEFILTERS('Dim - Date')
)
RETURN Result

-- [Sales $ LLY RT]
MEASURE [Sales $ LLY RT] =
VAR MaxMonth = MAX('Dim - Date'[MonthOrdinal]) - 24
VAR Result =
CALCULATE(
[Sales $ LLY],
'Dim - Date'[MonthOrdinal] <= MaxMonth,
REMOVEFILTERS('Dim - Date')
)
RETURN Result