Now do it in Power BI Desktop
Time intelligence: Power BI Desktop exercise
Create a proper date table in DAX, mark it, and build the classic time measures. You practised this in the browser; now do it in your own copy of Power BI Desktop (free from Microsoft), on the same data, and check your numbers below.
1. Get the data
In Power BI Desktop: Home → Get data → Text/CSV, pick each file, then Load. Check that numbers came in as numbers and dates as dates (Table view, column header icons).
Create the date table: Modeling → New table, paste this, then Mark as date table on the Date column. Sort Month by Month Number and Month Year by Month Key.
Dates =
ADDCOLUMNS(
CALENDAR(DATE(2013, 1, 1), DATE(2014, 12, 31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "mmm"),
"Month Number", MONTH([Date]),
"Quarter", "Qtr " & QUARTER([Date]),
"Month Year", FORMAT([Date], "mmm yyyy"),
"Month Key", YEAR([Date]) * 100 + MONTH([Date])
)In Model view, check or create these relationships (many side first):
- financials[Date] > Dates[Date]
2. The tasks
1. Sales by year
Show Sales by Year from your Dates table.
2. Year to date
Create Sales YTD and show it by Year (the December value is the year's total).
3. Same period last year
Create Sales LY with SAMEPERIODLASTYEAR, by Year. Why is 2013 blank?
3. Check your numbers
Try every task first. Then open each answer: the numbers below come from the same data, so yours should match exactly.
1. Sales by year
Total Sales = SUM(financials[Sales])| Year | Total Sales |
|---|---|
| 2013 | 26,415,255.51 |
| 2014 | 92,311,094.75 |
| Total | 118,726,350.26 |
2. Year to date
Sales YTD = TOTALYTD(SUM(financials[Sales]), Dates[Date])| Year | Sales YTD |
|---|---|
| 2013 | 26,415,255.51 |
| 2014 | 92,311,094.75 |
| Total | 92,311,094.75 |
3. Same period last year
Sales LY = CALCULATE(SUM(financials[Sales]), SAMEPERIODLASTYEAR(Dates[Date]))| Year | Sales LY |
|---|---|
| 2013 | (blank) |
| 2014 | 26,415,255.51 |
| Total | 26,415,255.51 |