Forum Discussion
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 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,
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.
2 Replies
-- [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 ResultI 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.
