This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	DAX (59 exercises)
Topic ==>	Calendars and dates (8 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Use Power BI's automatic calendar tables and time intelligence functions in DAX measures.

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.

Before you can do this exercise, you'll need to download and unzip this file .

Open the Music Tours - Basic Time Intelligence.pbix file in the folder shown above. On Page 1 you'll find a matrix containing the dates of shows and the tickets sold and revenue generated.

A slicer and matrix visual in Power BI Desktop

You can use the slicer to choose different artists and tours.

Add a measure to create a monthly running total of tickets sold using the TOTALMTD function.

Remember to reference the Date column of the automatic calendar table that is related to the Show date field, rather than just the Show date field itself.

Add the measure to the matrix.

A matrix visual in Power BI Desktop using a DAX measure to show a monthly running total.

Annoyingly, the running total makes dates on which no tickets were sold appear in the matrix.

To hide the dates on which no tickets were sold, update the measure to include an IF function which checks if the sum of tickets sold is blank.

A Power BI Desktop matrix visual displaying a DAX measure calculating a monthly running total.

The matrix should now hide any dates with no ticket sales.

Add another measure to calculate a monthly running total of show revenue. Add this measure to the matrix and make sure that it doesn't cause dates with no ticket sales to appear.

A matrix visual displaying DAX measures calculating running totals in Power BI Desktop.

You could apply some formatting to the measure.

On Page 2 you'll find a matrix showing the total length of tracks released as singles, grouped by year and month.

A matrix visual in Power BI Desktop grouped by year and month.

The matrix has been filtered to show singles released since 1980.

Create a measure which compares the sum of single length with the same period in the previous year. You can use a combination of the CALCULATE and SAMEPERIODLASTYEAR functions to do this. Add the measure to the matrix.

A matrix visual in Power BI Desktop grouped by year and month with conditional formatting.

You could add conditional formatting to highlight positive and negative numbers.

Add a measure which compares the sum of single length with the same value from one month ago. You can use the CALCULATE and DATEADD functions to do this. Add the measure to the matrix.

A matrix visual in Power BI Desktop with conditional formatting and DAX measures.

Again, conditional formatting might make it easier to read the results.

When comparing with the previous month, it doesn't make sense to display a value for the year. Try modifying the measure so that it only shows a result when the Month column is in scope.

You can use the ISINSCOPE function to check if the Month column of the Single release date field is in scope.

Save and close the report.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post