This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

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

Learn Power Pivot

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Software ==>	Power Pivot (43 exercises)
Topic ==>	Calendars and time intelligence (2 exercises)
Level ==>	Relatively easy
Subject ==>	Power Pivot training

Use Power Pivot's 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.xlsx file in the folder shown above. On Sheet1 you'll find a pivot table displaying a couple of measures.

A slicer and pivot table in Excel based on a Power Pivot data model

We'd like to analyse these values by year and month.

In Power Pivot, choose Design | Date Table | New to create a new calendar table. Rename this table as Show Calendar and create a relationship to the Show table.

Creating a relationship between two tables in a Power Pivot date model

Connect the Date column to the Show date column.

Add the Year, Month and Date fields to the pivot table on Sheet1.

An Excel pivot table grouped by date and showing DAX measures from Power Pivot

The table is now divided by dates.

Create a measure using the TOTALMTD function to calculate a monthly running total of tickets sold. Add the measure to the pivot table.

A pivot table displaying DAX measures created in Excel Power Pivot

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

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 pivot table showing data on concert ticket sales using a calendar table in a Power Pivot data model

The table 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 pivot table and make sure that it doesn't cause dates with no ticket sales to appear.

An Excel pivot table showing concert ticket sales by date using a Power Pivot data model

Apply some formatting to the measure.

On Sheet2 you'll find a pivot table showing the total length of tracks released as singles.

An Excel pivot table showing a single DAX measure from a Power Pivot data model

We'd like to group this measure by date.

Create a new calendar table called Track calendar and connect it to the Track table.

Creating a relationship between tables in a Power Pivot data model

Connect the Date column to the Single release date column.

Add the Year and Month columns from the Track Calendar table to the pivot table on Sheet2. Use the Row Labels filter to exclude blanks and choose to show subtotals at the top of a group.

An Excel pivot table using a Power Pivot calendar table to slice a DAX measure by year and month

Each year should show a subtotal for the measure.

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 pivot table.

A pivot table with conditional formatting using a Power Pivot data model

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 pivot table.

An Excel pivot table displaying DAX measures with conditional formatting

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 Track Calendar table is in scope.

Check that the subtotals don't appear for years for this measure.

An Excel pivot table showing DAX measures from Power Pivot

You should still see subtotals for the first two measures.

Save and close the file.

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:

Learn Power Pivot

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

POWER PIVOT EXERCISES

POWER PIVOT EXERCISES

Use Power Pivot's time intelligence functions in DAX measures.

Head office

London

Manchester