This is my DAX Query solution to Maven Analytics Data Drill #3 – Rolling Up, Looking Back.
The challenge is to find the difference in MoM sales values from April to May for the Astoria location.
More information to the challenge can be found on their website.
For this Data Drill the request and solution is relatively simple in nature, as any BI analyst, in any capacity, will encounter some form of Month-on-Month time intelligence request.
The solution is as follows:
First, we define our measures. Total Sales, Previous Month Sales and the Abs Sales (actually just values, not the mathematical absolute of anything).
Total Sales is just the sum of sales.
PM Total Sales (Previous Month Total Sales) uses the CALCULATE() and the DATEADD() function, The expression to be evaluated is Total Sales, but going back one-month exactly, for each date in the table. e.g. 15-Feb-25 would now be 15-Jan-25.
Abs_Sales (Absolute Sales) finds the difference between the current sales and the previous month sales.
/*-------------Maven Analytics - Data Drills-------------*/
/*----Data Drills - 3 - Rolling Up, Looking Back----*/
DEFINE
MEASURE Box[Total Sales] = SUM( Coffee[sales] )
MEASURE Box[PM Total Sales] =
CALCULATE(
[Total Sales],
DATEADD( Calendar[Date], -1, MONTH )
)
MEASURE Box[Abs_Sales] = [Total Sales] - [PM Total Sales DA]Thereafter, we now need to query the data, grouping by Year-Month, and Store.
To minimise the number of rows, we filter the output query to get only the months in question, as well as the store.
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
Calendar[Year-Month],
Coffee[store],
"Total Sales", [Total Sales],
"PM Total Sales DA", [PM Total Sales DA],
"Abs Sales", [Abs_Sales]
),
Calendar[Date] >= DATE( 2023, 4, 1 ),
Calendar[Date] < DATE( 2023, 6, 1 ),
Coffee[store] = "Astoria"
)
ORDER BY
Calendar[Year-Month],
Coffee[store]
Query Result
Submitting this on the website
Leave a Reply