WISE OWL EXERCISES
POWER BI EXERCISES
POWER BI EXERCISES
- Basic reports (10)
- Data sources (2)
- Query editor (8)
- Multiple tables (1)
- Filtering data (9)
- Drill-through filtering (1)
- Bookmarks (3)
- Charts (10)
- Other types of visualisation (1)
- Overview of maps (5)
- Calculated columns (7)
- Introduction to DAX (3)
- Creating measures in DAX (1)
- Calendars (2)
- Date functions (2)
- Roles and security (1)
- Advanced data models (4)
- Drill-through and bookmarks (3)
- Custom visuals (3)
- Parameters (5)
- Quick measures (3)
- Report themes (2)
- BI mobile (1)
- Power BI Templates (1)
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).
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:
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!
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:
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:
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 ...).