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 Wise Owl course listed below!

Category ==> Power BI Desktop  (26 exercises)
Topic ==> Date functions  (2 exercises)
Level ==> Relatively easy
Course ==> Power BI Desktop
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:

Matrix of sales by quarter

You'll need to create a relationship between the purchase date (tblPurchase) and calendar date (tblCalendar) fields, and also apply a filter to the visualisation to show only figures for purchase date year 2015.

  

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

Quarter to date

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