Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PlentyL
Helper II
Helper II

How to get a bar chart showing rolling 12 months

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? 

 

PlentyL_0-1644573753848.png

 

thanks in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

Screenshot 2022-02-11 111705.png

  • 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!

 

View solution in original post

5 REPLIES 5
PlentyL
Helper II
Helper II

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.

PlentyL_0-1644575752221.png

 

@PlentyL  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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Amazing, thanks so much! All sorted. =D

Anonymous
Not applicable

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.

Screenshot 2022-02-11 111705.png

  • 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
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.