Power BI Project · Advanced · 4 to 6 hours
Time intelligence measures in Power BI
A proper date table, then YoY, YTD, margin, and a moving average in DAX. The measures every analyst is expected to write, and the filter-context reasoning behind them.
The brief
A coffee retail chain runs five stores and sells across four categories, two full years of daily sales. Leadership wants a report that answers time questions on demand: how are we tracking year over year, what is year-to-date, what is margin, and what does the trend look like once the December spike is smoothed. This is a DAX time-intelligence project, and it starts where most beginners stumble: without a dedicated, marked date table, functions like SAMEPERIODLASTYEAR and DATESYTD quietly return the wrong answer.
Your role
You are the BI analyst. Deliverable: a one-page Power BI report with correct time-intelligence measures and a short note on the trend behind the seasonality.
The dataset
731 days (Jan 2024 to Dec 2025) of daily sales for 5 stores and 4 categories
powerbi-daily-sales.csv · 14,620 rows
Columns: date, store, category, units, revenue, cost
Setup
- Open Power BI Desktop (free) and load powerbi-daily-sales.csv.
- Create a separate Date table covering the full range and mark it as the date table.
- Relate the Date table to the sales table on the date column.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Build a dedicated Date table and mark it, so time-intelligence functions work correctly.
- 2Write base measures: Total Revenue, Total Cost, and Margin as measures, not calculated columns.
- 3Write Margin % as DIVIDE([Margin], [Revenue]) and confirm it against the total.
- 4Write Revenue PY with CALCULATE and SAMEPERIODLASTYEAR, then YoY % with DIVIDE.
- 5Write a YTD measure with DATESYTD or TOTALYTD and validate it at year end.
- 6Add a trailing 3-month moving average measure to smooth the December spike.
- 7Assemble the report: KPI tiles, a YoY trend, and the moving average line, then note the underlying trend.
Work like an AI-powered analyst
The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.
- Ask ChatGPT or Claude why SAMEPERIODLASTYEAR needs a marked, gap-free date table, and what breaks without one.
- Paste a measure that returns the wrong number and have the AI walk the filter context with you rather than just fixing it.
- Have the AI explain why margin must be a measure, not a calculated column averaged across rows.
Expected output
- A one-page report with correct YoY, YTD, margin, and moving-average measures.
- A marked Date table driving all time-intelligence calculations.
- Measures (not columns) that recompute correctly at every level.
- A short note reading the trend once seasonality is smoothed.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- The data is 14,620 rows over 731 days, from Jan 1 2024 to Dec 31 2025.
- Total revenue is about 107.39 million pesos, cost about 48.22 million, margin about 59.17 million, a margin of 55.1 percent.
- Year-over-year revenue growth is 11.9 percent (2024 about 50.68 million, 2025 about 56.71 million).
- The single best month is December 2025 at about 6.43 million pesos, the seasonal peak.
- A 3-month moving average should flatten that December spike and reveal the steadier underlying climb.
Skills check
6 advanced questions on the judgement this project demands, not just its steps. Your score saves to your dashboard, and you can retry for a better one.
Finished it? Put it in your portfolio.
This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.