How to get a bar chart showing rolling 12 months

[Anonymous]

Hi All,

I have several graphs like the one below with Jan- Dec data 2021, however i am now trying to load Jan 2022 data and it just adds to the Jan bar, therefore combining 21&22. How can i get my graphs set to show the last rolling 12 months? 

thanks in advance.

[Anonymous]

Brilliant, thanks both that has worked.

January now just contains Jan 22. 1 last question - although Jan now only includes 2022, its not visually clear. Is there a way to get the year number to display after the month label or to get it to display in order, eg. Jan 22 would be at the far right not left? thank you.

[AllisonKennedy]

@Anonymous  As I mentioned in my original reply, add the Year to your Axis in your visual and then reformat it. I've just written a blog for you here: 

https://excelwithallison.blogspot.com/2022/02/power-bi-formatting.html 

[Anonymous]

Amazing, thanks so much! All sorted. =D

[Anonymous · Accepted solution]

Hi Plenty

You have two options here:

• Filters pane: move yout date field into the filter pane, and select a Relative date filter type showing the items included in the last 12 months.

• Create a measure: by using CALCULATE() function, you can implement a measure like the following (please note it is not literally DAX code):

CALCULATE( YourMeasure, Date <= TODAY() and Date >= TODAY() - 12 months)

CALCULATE( YourMeasure, DATESBETWEEN(DateField, TODAY()-12 months, TODAY())

Hope it helps, regards!

[AllisonKennedy]

@Anonymous do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

If so, add the DimDate[Date] column as a filter on this visual and set to relative date > Last 12 months (calendar) if you want to see completed months only. 

You should also add DimDate[Year] to the Axis of the visual above month so that they are sorted properly, and in the formatting options for X-axis, set Concatenate labels to Off (you may need to change sort of axis to sort by Year Month > Sort Ascending before it will let you turn concatenate labels to off)