Practice and Answer Key: A Two-Month Data Model

Suppose Rani tracks three fictional stores in Kolhapur, Sambhaji Nagar and Pune for October and November 2026. All data is fictional practice data. Amount = Qty × Price (add it as a column in Orders before loading).

Exercise (one under the other):

1. Type the three tables, make them Excel Tables named Orders, Stores and Products, and add Amount to Orders.
2. Load all three into the Data Model and create the two relationships in Diagram View.
3. Build a Calendar table for 01-10-2026 to 30-11-2026, relate it and mark it as the date table.
4. Create the measures Total Sales, Customers, Amazon Now Sales, Sales LM and MoM %.
5. Build a PivotTable from the Data Model: City in Rows, Calendar[Month] in Columns, Total Sales in Values.
6. Write a CUBEVALUE for Kolhapur in November.

Expected result (check your numbers):

Total Sales       := SUM(Orders[Amount])
Customers         := DISTINCTCOUNT(Orders[CustomerID])
Amazon Now Sales  := CALCULATE([Total Sales], Stores[Platform] = "Amazon Now")
Sales LM          := CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH))
MoM %             := DIVIDE([Total Sales] - [Sales LM], [Sales LM])
CUBE cell         : =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Stores].[City].&[Kolhapur]","[Calendar].[Month].&[Nov]")

Common mistakes:

Ravindra Bagale's rule: Tables first, relationships second, measures third, visuals last.