Power BI Quizzes

Create workbook visual showing MOM % change

Monday, May 10th, 2021

Open a new workbook and connect to the shipping cost data,

Create a date table:

The CALENDARAUTO function will create a table with a single column named “Date” that contains a contiguous set of dates based on the dates already in the model… 1/1/2020 is the first date in Shpping_Cost and 11/9/2021 is the last date, so the range of dates will be from 1/1/20 to 11/9/21.

Add a table to the view:

Create the relationship:

Add shipping cost, Month & Year to the view:

Sort by Year

In the Values area, move Shipping Cost after the date fields:

Now add the % MOM calculation. The variable “PriorShipping” to determine the shipping cost from the previous month:

% MOM Shipping Cost Change = 
var PriorShipping = CALCULATE(sum('Shipping Cost'[Shipping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))
return divide(sum('Shipping Cost'[Shipping_Cost]) - PriorShipping,PriorShipping)

Adding the new measure to the view, we should see something like this:

A few things to fix here:

1. Sort on Year then Month
2. Change the format to a percentage
3. Hide the total row for % MOM Change

1. Sort on Year then Month

2. Change the format to percentage

Switch the the model tab, select the % MOM change field, and change the format using the dropdown.

3. Hide the total row for Percent Change

This can be done by modifying “% MOM Shipping Cost Change” to only return the % change when the ISFILTERED function returns true.

% MOM Shipping Cost Change = 
var PriorShipping = CALCULATE(sum('Shipping Cost'[Shipping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))
return if(ISFILTERED('Date Table'[Date].[Month]),
    divide(sum('Shipping Cost'[Shipping_Cost]) - PriorShipping,PriorShipping)
)

Download to Power BI workbook

Link to shipping cost data

Create a Power BI visual showing current month next to same month from prior year

Friday, May 7th, 2021

I created sample data with a date column and a numeric column

Load the data in Power BI and create a table showing shipping cost by month:

We need a date table. Create one here:

Use this DAX expression to create the date table:

Date Table = CALENDARAUTO(12)

Link the tables:

Relationship should be many to 1 (*:1)

Then add a new DAX measure:

Prior Month SPrior Month Shipping Cost = CALCULATE(sum('Shipping Cost'[Shpping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))

Add the new measure to the view:

Sort ascending on year:

You can see that March 2020 shows $4.24 for the Shipping cost. April 2020 shows $4.24 for the prior month shipping cost.

Look what happens if use use the date from shipping cost instead:

Prior Month Shipping Wrong = CALCULATE(sum('Shipping Cost'[Shpping_Cost]),PARALLELPERIOD('Shipping Cost'[Date],-1,month))