Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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.
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.
-- [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
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |