Learn Power BI

  • Blog
  • Practice Questions
  • Nanodegree

Learn Power BI

Accelerate your Career and Get Certified

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:

This image has an empty alt attribute; its file name is image-12.png

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))

About the Author: Lukas Halim

I earned a Masters in Analytics from NC State in 2013 and now work as a data analyst in the health insurance industry. Using tools such as SAS, Tableau, and Teradata, I conduct analysis to help my company improve healthcare affordability and customer engagement.

  • Learn More
  • 4.6 Instructor Rating

  • 53,604 Students

  • 14,612 Reviews

  • 2 courses

© Power Bi Skills. All rights reserved.
Privacy Policy | Design by Liscio Apps, LLC