This is a common Power BI (DAX) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A sales dashboard allows business users to analyze monthly sales performance. The Power BI semantic model contains: FactSales (40 Million Rows) DimDate DimProduct DimRegion DimCustomer The report displays monthly sales in a Matrix visual. Business users want to display: Current Month Sales Previous Month Sales Month-over-Month (MoM) Growth Amount Month-over-Month (MoM) Growth Percentage The calculations should automatically respond to report filters such as Region, Product and Customer. As the Power BI developer, answer the following questions: How would you calculate Previous Month Sales using DAX? Which Time Intelligence function would you use and why? How would you calculate Month-over-Month Growth Amount? How would you calculate Month-over-Month Growth Percentage? What happens if the current month is the first month in the dataset? Will Region, Product and Customer filters still apply? Explain. How would you validate the calculations? What prerequisites must exist before using Time Intelligence functions?

Answer & Explanation

**1. Calculate “Previous Month Sales”**

Create a measure that returns the total sales for the month that is immediately before the month currently in the filter context.

```DAX
Previous Month Sales :=
CALCULATE (
[Total Sales] , -- your base sales measure
PREVIOUSMONTH ( DimDate[Date] )
)
```

*Why this works* – `CALCULATE` changes the filter context. `PREVIOUSMONTH` returns a table of dates that belong to the month directly preceding the month(s) already filtered by the visual or slicer. By feeding that date table to `CALCULATE`, the fact table is re‑filtered to those earlier dates, and the same `[Total Sales]` logic is applied.

---

**2. Which Time‑Intelligence function and why?**

`PREVIOUSMONTH` is the most appropriate function for a month‑over‑month comparison because:

* It automatically handles month boundaries (e.g., Jan → Dec of the prior year) without extra logic.
* It respects the current filter context (region, product, customer, etc.) and only replaces the date filter.
* It returns a **single‑column table of dates**, which `CALCULATE` can consume directly.

Other functions such as `PARALLELPERIOD` or `DATEADD` could also be used, but they require you to specify the offset and are more error‑prone for a simple “previous month” need. `PREVIOUSMONTH` is concise, self‑documenting, and optimized for this scenario.

---

**3. Calculate “Month‑over‑Month Growth Amount”**

The growth amount is simply the difference between the current month’s sales and the previous month’s sales.

```DAX
MoM Growth Amount :=
[Total Sales] - [Previous Month Sales]
```

Because both measures already respect all non‑date filters, the subtraction automatically reflects the same region/product/customer context.

---

**4. Calculate “Month‑over‑Month Growth Percentage”**

Percentage growth must guard against division by zero (or a blank previous month). Use `DIVIDE`, which returns BLANK instead of an error when the denominator is 0 or BLANK.

```DAX
MoM Growth % :=
DIVIDE (
[MoM Growth Amount] ,
[Previous Month Sales] ,
BLANK() -- optional alternate result, e.g., 0
)
```

If you prefer to show “0 %” when there is no prior month, replace `BLANK()` with `0`.

---

**5. What if the current month is the first month in the data set?**

When the filter context contains the earliest month, `PREVIOUSMONTH` finds no earlier dates and returns an empty table. Consequently:

* `Previous Month Sales` → **BLANK**
* `MoM Growth Amount` → **BLANK** (because subtraction with BLANK yields BLANK)
* `MoM Growth %` → **BLANK** (DIVIDE of BLANK by BLANK returns BLANK)

If the business wants a different display (e.g., “‑” or “0”), wrap the measures with `IF ( ISBLANK ( … ), 0, … )` or use `COALESCE`.

---

**6. Do Region, Product and Customer filters still apply? Explain.**

Yes. All three filters continue to affect the calculations because:

* The only filter that `PREVIOUSMONTH` changes is the **date** filter.
* `CALCULATE` retains any existing filters on other columns (Region, Product, Customer) unless you explicitly remove them.
* The relationship between `DimDate` and `FactSales` is active, so the date filter propagates to the fact table while the other dimension filters stay intact.

Therefore the measures always show “previous month” numbers **within the same slice** of region/product/customer that the user has selected.

---

**7. How to validate the calculations**

1. **Spot‑check against source data**
*Write a simple SQL query* that aggregates sales by month, region, product, and customer for two consecutive months. Compare the results with the values shown in Power BI for the same filter combination.

2. **Use a matrix visual**
*Add the four measures (Current, Previous, Growth Amount, Growth %)* side‑by‑side for a few months. Verify that `Growth Amount = Current – Previous` and that the percentage matches the manual calculation.

3. **Test edge cases**
*Select the earliest month* – all “previous month” measures should be blank.
*Select a single region* or a single product – the numbers should still line up with the filtered SQL results.

4. **DAX Studio / Performance Analyzer**
Run the DAX queries for the measures and inspect the generated query plan. Ensure that the date table is being used and that there are no unexpected cross‑joins.

5. **Consistency check**
Sum the “Current Month Sales” across all months and compare it to the total sales measure without any date filter. The sum of month‑by‑month values should equal the overall total (unless you have a non‑continuous date table).

---

**8. Prerequisites for using Time‑Intelligence functions**

| Requirement | Why it matters |
|-------------|----------------|
| **A dedicated Date table** (`DimDate`) that contains a row for every calendar day covering the full range of your fact data. | Functions like `PREVIOUSMONTH` operate on a continuous set of dates; missing days break the logic. |
| **Date column marked as “Date” data type** and set as the **primary date column** in the model. | Power BI needs to know which column represents the calendar. |
| **Date table marked as the official “Date Table”** (Model → Date Table → Mark as Date Table). | This tells the engine to treat the table as the source for all time‑intelligence calculations. |
| **Active relationship** between `DimDate[Date]` and `FactSales[DateKey]` (one‑to‑many, single direction). | Without an active relationship, the date filter cannot flow to the fact table, and the functions will return blanks. |
| **Continuous, gap‑free date range** (no missing days). | Gaps cause `PREVIOUSMONTH` to skip months or return unexpected blanks. |
| **Granularity alignment** – the date column used in the visual must be at the same granularity (month) or a lower one (day). | If the visual filters by a non‑date column only, the time‑intelligence functions will use the **all‑dates** context, which may not be what you expect. |

If any of these conditions are not met, the time‑intelligence functions may return incorrect results, blanks, or performance penalties.

---

**Putting it all together – a quick reference of the final measures**

```DAX
Total Sales :=
SUM ( FactSales[SalesAmount] )

Previous Month Sales :=
CALCULATE ( [Total Sales] , PREVIOUSMONTH ( DimDate[Date] ) )

MoM Growth Amount :=
[Total Sales] - [Previous Month Sales]

MoM Growth % :=
DIVIDE ( [MoM Growth Amount] , [Previous Month Sales] )
```

Add these four measures to your matrix, place `DimDate[Month]` (or a month hierarchy) on the rows, and the slicers for Region, Product, and Customer will automatically filter every column, giving the business the exact month‑over‑month view they need.