Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is developing a Power BI model for a retail company. The model includes a 'Sales' table with 'SaleAmount' and 'OrderDate', and a 'Date' table marked as a date table. The analyst needs to create a measure that calculates the 'Sales Growth Percentage' compared to the previous month. The calculation should be `(Current Month Sales - Previous Month Sales) / Previous Month Sales`. Which DAX expression correctly calculates the 'Sales Growth Percentage'?
- A`VAR CurrentMonthSales = SUM(Sales[SaleAmount]) VAR PreviousMonthSales = CALCULATE(SUM(Sales[SaleAmount]), PREVIOUSMONTH('Date'[Date])) RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, PreviousMonthSales)`
- B`VAR CurrentMonthSales = SUM(Sales[SaleAmount]) VAR PreviousMonthSales = CALCULATE(SUM(Sales[SaleAmount]), PREVIOUSMONTH('Date'[Date])) RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, CurrentMonthSales)`
- C`VAR CurrentMonthSales = SUM(Sales[SaleAmount]) VAR PreviousMonthSales = CALCULATE(SUM(Sales[SaleAmount]), SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, PreviousMonthSales)`
- D`VAR CurrentMonthSales = SUM(Sales[SaleAmount]) VAR PreviousMonthSales = CALCULATE(SUM(Sales[SaleAmount]), DATEADD('Date'[Date], -1, MONTH)) RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, CurrentMonthSales)`
Show answer & explanationAnswer & explanation
Correct answer: A. `VAR CurrentMonthSales = SUM(Sales[SaleAmount]) VAR PreviousMonthSales = CALCULATE(SUM(Sales[SaleAmount]), PREVIOUSMONTH('Date'[Date])) RETURN DIVIDE(CurrentMonthSales - PreviousMonthSales, PreviousMonthSales)`
Option C correctly uses `PREVIOUSMONTH('Date'[Date])` to shift the filter context to the previous month for the 'PreviousMonthSales' variable. It then correctly applies the growth percentage formula `(Current - Previous) / Previous` using `DIVIDE` to handle potential division by zero.
Why the other options are wrong
- B. The division formula is incorrect; it divides by `CurrentMonthSales` instead of `PreviousMonthSales`.
- C. `SAMEPERIODLASTYEAR` calculates sales for the same period in the previous year, not the previous month.
- D. `DATEADD` with -1 month is functionally similar to `PREVIOUSMONTH`, but the division formula is incorrect as it divides by `CurrentMonthSales`.
Sales Growth Percentage (MoM)
A measure calculating the percentage change in sales from the current month compared to the previous month.
- Requires calculating current month sales.
- Requires calculating previous month sales using time intelligence functions.
- Formula: (Current Month Sales - Previous Month Sales) / Previous Month Sales.
Memory trick: Current Minus Previous, Divide By Previous, Growth is Obvious.