How can I create a line that shows the 5 year average of a pivot table data set that's broken down by month?

Question

I am new to using DAX but have gone down that rabbit hole today trying to solve this. The dates of the pivot table are grouped and set up so that the Month is in the Rows section of the pivot table and Years are the columns. Ideally, I would like to have a "Grand Total" column that shows the average number seen in each of the 5 years prior to the current one on a monthly basis and then add this column to the relevant PivotChart in order to compare the monthly data I have seen this year to the average seen over the prior 5 years. Is there a way to do this with DAX?

I tried creating a Power Pivot Measure and the adding the measure to the Values section of the pivot table. The data I wanted to see was joined by an additional column in each year for the 5-year average, which I do not want.

=CALCULATE(
AVERAGE([Crude (excluding SPR)]),
PARALLELPERIOD(US_Stocks_Monthly_Avg[Date],-5,YEAR))

Added Details #1

Below is a screenshot of the pivot table in its current form. This pivot table is called: PivotTable1

[Current pivot table layout] enter image description here

Below is a screenshot of the table that the pivot table is pulling data from. This table is called: US_Stocks_Monthly_Avg

[Data source table] enter image description here

Added Details #2

I have created a separate table for consecutive dates going from oldest to largest that you can see below. This table is called: Monthly_Dates_Table

Monthly_Dates_Table screenshot

I then added this table to the Data Model and created a relationship between the Date field in the data source table and the new dates table. An image of this relationship in the Power Pivot Data Model can be seen below. The arrow points away from the dates table ("Monthly_Dates_Table") and towards the data source table ("US_Stocks_Monthly_Avg"). Is that the correct direction for the connection? I have also attached an image of the data view of the Monthly_Dates_Table in the Data Model.

Data Model Diagram View

Data Model Data View

I tried to create the Measures I am looking for but was confused about which table I should be referring to for time intelligence purposes now that there are two tables with dates.

I added the Date field from the Monthly_Dates_Table to the pivot table and proceeded to group the dates together by month and year and create the same layout as before that refers to the dates in the Monthly_Dates_Table. Let me know if this step was premature.

[Current pivot table v2] enter image description here

Post an image of your data model (diagram view) so we can see how you have the fact table and the dates table linked. If you want to do time intelligence, you need to have those separated (AFAK). — Ricardo Diaz
Thank you for your response Ricardo. I have updated the post to include screenshots of both the current layout of the pivot table and the table that houses the data used in the pivot table. I am a little unclear on whether this layout has sufficiently separated the fact table and dates table. Let me know your thoughts. I appreciate your help. — Jnash33
Create a separate table for consecutive dates from oldest (12/1/2017) to newest (9/1/2019) and add that table to the data model. Then create a relationship between the Date field in the data source table and the new dates table. Then we'll create the measures. It's important that you edit the post after these steps mentioned so we can check the Data Model and advice on the proper measure's syntax. Check this link: support.office.com/en-us/article/… and youtube.com/watch?v=TIGehGTZDYA — Ricardo Diaz
Thank you Ricardo. I have proceeded with the steps you recommended and updated the post to reflect this progress. The links you shared helped me better grasp the concepts involved in this so I appreciate you sharing those.I tried to jump slightly ahead to what I figured were the next steps to see if I could cut down on the number of basic question I am asking of you but didn't seem to make much progress on my own. Let me know your thoughts on the best next steps. Thanks again for your help. — Jnash33

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2019-10-08T19:53:49

Jnash33 try this:

For my explanation: I set up the tables like this

1) In Power Pivot I used the date table functionality:

Note: Even though my screen is in spanish, you can locate how to create a dates table easily

2) Then I created the relationship between the dates fields:

3) After that, I added the average measure:

5YearAverage:=CALCULATE(AVERAGE(US_Stocks[Total crude and petroleum products]),DATESINPERIOD(Calendario[Date],LASTDATE(Calendario[Date]),-5,MONTH))

Note: See this article from Radacad's blog: https://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bi

4) Finally I added the Pivot Table (remember to use the data model tables)

Note: Adjust the measure and see in this case I used "month" as the last argument and it takes the previous 5 months and the current one.

Let me know if this helps and remember to mark the answer to help others.

How can I create a line that shows the 5 year average of a pivot table data set that's broken down by month?

1 Answers