Power BI | Date functions exercise | Create a calendar and show cumulative figures to date

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the relevant Wise Owl course (sadly, only in the UK for now).

Software ==> Power BI  (88 exercises)
Version ==> Latest update
Topic ==> Date functions  (2 exercises)
Level ==> Relatively easy
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

If you haven't already done so, run the SQL script in the above folder in SQL Server Management Studio to create the Make-a-Mammal database.

Create a new Power BI file, and load the following tables:

Tables to load

These are the tables you'll need to do this exercise.


Create a matrix to show total sales by quarter and month for 2015, reading the notes below carefully!

Filtered matrix

You'll need to create a relationship between the purchase date (tblPurchase) and calendar date (tblCalendar) fields, apply a filter to the visualisation to show only figures for purchase year 2015, click on the icon shown selected to show both the quarter and month together, and suppress row totals in the matrix by turning the relevant formatting property off.


Create and show a measure to show quarter-to-date figures:

Quarter to date figures

You can either use the TOTALQTD function, or the CALCULATE function (restricting the dates used with the DATESQTD function).


Now add another measure which shows for each time period what the sales were in the previous quarter:

Previous quarter

Use the CALCULATE function to sum quantity, but using the DATEADD function to go back one quarter in time from the filter context's dates.


Save this file as Tinder, then close down the Power BI instance you're using (if you're wondering about the file name, it generates good dates if used properly ...).

This page has 0 threads Add post