Log In
11

Now do it in Power BI Desktop

Advanced DAX: Power BI Desktop exercise

The patterns analysts reach for every week: running totals, virtual relationships and the inactive date. 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]
  • financials[Ship Date] > Dates[Date] (inactive)

2. The tasks

  1. 1. Running total

    Create Running Sales: everything up to the last date in context, across years. Show it by Year.

  2. 2. Sales by ship date

    Turn on the inactive Ship Date relationship in one measure, and show it by Year.

  3. 3. Targets without a relationship

    Targets has no relationship. Use TREATAS so each country row shows its own total target.

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. Running total
Running Sales = CALCULATE(SUM(financials[Sales]), FILTER(ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))
YearRunning Sales
201326,415,255.51
2014118,726,350.26
Total118,726,350.26
2. Sales by ship date
Sales by Ship Date = CALCULATE(SUM(financials[Sales]), USERELATIONSHIP(financials[Ship Date], Dates[Date]))
YearSales by Ship Date
201325,275,493.21
201492,354,666.26
Total118,726,350.26
3. Targets without a relationship
Country Target = CALCULATE(SUM(Targets[Target]), TREATAS(VALUES(financials[Country]), Targets[Country]))
CountryCountry Target
Canada26,900,000
France22,900,000
Germany26,400,000
Mexico21,200,000
United States of America24,300,000
Total121,700,000

The lessons behind this exercise