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