Forum Discussion
Multiple Running Totals to Compare Different Years
- 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 yearNext 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 ResultFo the final result I finally got by graph that I was looking for.
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.