Dynamic LAST & PREVIOUS 12 Months in a Chart and Table | Power BI

One thing I’ve found, in my many years of creating reports in Excel and then Power BI, is when working with monthly reports for clients they were static, as of that month, looking back 12 months and the only way clients could see the previous month's last 12 months was to look at an old report.

This is what led me to find a way to change that by creating moving last 12 months based on the month and year selected and in this video, I show you how.

#powerbi #dax #powerquery

📁 Dataset for Sales Data and file for original dim date table M code

▶️ Link to Rick’s original video I learnt the DAX from for last 13 months

▶️ Video clips used in intro

🔖c h a p t e r s 🔖

00:00 - Intro
00:36 - Overview of how it works
02:28 - Create a copy of your date table
02:28 - Create 'MonthYear' column
03:44 - Create 'MonthYear' sort column
04:14 - Set sort for 'MonthYear' column
05:25 - Create last 12 months dax measure
11:28 - Create previous last 12 months measure
12:54 - High and low conditional formatting
16:21 - Add measure to conditional format
17:32 - Create dynamic title measure
18:20 - Add dynamic title measure to title

[SiweiJiao]

Very helpful, thank you! A quick question - how to display the grand totals for 12 month in the bottom of table?

[VeronicaOConnell-ofog]

Thanks for this video, exactly what I needed. I am having an issue though with how to create additional Measures. My data set is around Login Data vs Sales and I would like to know how to calculate retention for those that logged in the current R12 and the Previous year R12. Do you have additional videos or know how I might be able to do that calculation?

[helloworldtt]

thanks for teaching, its amazing! however, how come the current month do not show once i choose a location in my location slicer. That location has data for sales though

[lolliMAN]

Thanks for this video! Although I can have a dynamic chart now, I still have to select all 12 months I want to see. It doesn't show 12 months leading up to say Jan'24 if I only select Jan'24. For some context: my Excel source has a table that calculates YoY % Change. I only want to have a single column chart showing the last 12 months of changes. I have done everything you have, except for the P12M, YoY and YoY %, which I don't need. My axes are the same (MonthYear from slicer query, L12M from data query), what is going wrong?

[timothygrom]

How do you connect the slicer date to your table and display the same 12 month period as your graph (Last 12 months)?

[WorldStory_]

Hi there, i try this working out. By the way, when add filter year, you filter the first month of year, it just display only 1 bar, how to fix this ?