📎 This article includes 3 downloadable practice files ↓
Sales has two dates, OrderDate and ShipDate, but only one relationship to the Date table can be active. Make OrderDate active and ShipDate inactive (dotted line), then switch per measure:
Sales by Ship Date = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )
Avg Days to Ship = AVERAGEX ( Sales, DATEDIFF ( Sales[OrderDate], Sales[ShipDate], DAY ) )| FY 2025-26 | Value |
|---|---|
| By order date | ₹1,53,75,449 |
| By ship date | ₹1,57,25,506 |
| Orders placed in FY 2024-25, shipped in FY 2025-26 | 12 |
| Average days to ship | 5.08 |
The difference matters for accounting: revenue is often recognised on dispatch, not on order.
Practice
Import pbi-intermediate-model.xlsx into Power BI Desktop (free), create the relationships listed at the top of the .dax file, mark the Date table as a date table and add this lesson’s measures. Compare your card and table values with the lesson’s sheet in the expected-results file. Those numbers were worked out independently in Python from the same data, so a mismatch means a filter or relationship to check.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 61 KB)
- 📗Practice model (Excel)Two years of sales (700 orders, FY 2024-25 and 2025-26) with Customers, Products, a full Date table, Targets and RegionUsers.⬇ XLSX · 63 KB
- 📄Lesson 6 measures (.dax)Every measure from this lesson, ready to paste into Power BI Desktop.⬇ DAX · 909 B
- 📗Expected resultsThe numbers your measures should show, one sheet per lesson.⬇ XLSX · 11 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.