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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBI_Newb
Frequent Visitor

How can I get a YEARLY chart to display 12 months of rolling data (based on a date filter)

I'm wondering if someone might be able to help with Power BI Desktop?

 

I have two challenges:

 

#1 - I need to create a yearly chart of sales data showing 5 years of rolling 12 months of data – using the end date (from the date filtered by the user).

Eg. If the user selects 12-feb-2020, then the chart will display data from 13-feb-2015 to 12-feb-2020.

 

Therefore, the chart should display a total of sales for these periods:

13-feb-2015 to 12-feb-2016

13-feb-2016 to 12-feb-2017

13-feb-2017 to 12-feb-2018

13-feb-2018 to 12-feb-2019

13-feb-2019 to 12-feb-2020

 

#2 - I also need the chart to show the x axis in the format YYYY-MM (eg. 2016-02, 2017-02, 2018-02, 2019-02, 2020-02).

So ideally the chart would look something like this :

PowerBI_Newb_1-1612146094768.png

I've been able to do this in Power BI Report builder using switch to reformat the data....but I'm totally confused as to how to do it in Power BI.

 

I've created a calendar table (based off a youtube video I found) and linked the sales data to this table, but I'm stumped as to how to create a dynamic rolling date

 

Any and all help will be graciously appreciated 🙂

 

Here’s the sample data with calendar table (PBIX file):

https://www.dropbox.com/s/pi00a9owt61p13k/Sales%20Data%20Sample.pbix?dl=1

 

Many thanks.

13 REPLIES 13
v-cazheng-msft
Community Support
Community Support

Hi, @PowerBI_Newb 

Is your problem solved? If there are some posts help, then please consider Accept them as the solutions to help the other members find them more quickly.

 

Best Regards,

Caiyun Zheng

Hi, it's not complete yet. I still need to figure out the second part of the question: 
How to change the label on the X axis. Thanks

lbendlin
Super User
Super User

So your user selects 12-Feb-2020.  You can now either show periods from the 13th of the prior month to the 12th of the current month, or you can show months.  Things like "2020-03" are meaningless until you decide which one to do (and they are probably confusing the users if you choose to go with the first variation).

 

You should not allow the users to select a date. Force them to select a month.

 

Hey lbendlin - thanks for responding.

Our sales guys need to run the reports from a set date range, not month - and the report will have a start and end date shown...probably should have mentioned that.

Also, I'm not quite sure what you're saying - I'm looking to show 12 months worth of data for each bar (not one month), eg:
2016-02 shows data from: 13-feb-2015 to 12-feb-2016
2017-02 shows data from: 13-feb-2016 to 12-feb-2017
2018-02 shows data from: 13-feb-2017 to 12-feb-2018
2019-02 shows data from: 13-feb-2018 to 12-feb-2019
2020-02 shows data from: 13-feb-2019 to 12-feb-2020

Thanks again

what I am saying is that 12-Feb-2016 is not at all equivalent to  2016-02 .  You are mislabeling your chart and your users will make wrong assumptions.

Ah ok - gotcha. Thanks for pointing that out

VijayP
Super User
Super User

@PowerBI_Newb 

Do you want this?!

VijayP_0-1612148576889.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi Vijay,

 

No, I'm actually looking to display 12 months of data per bar, not 1 month of data.

I want it to look something like this:

PowerBI_Newb_0-1612150468472.png

IE. 2021-03 would have a sum of all sales data for the previous 12 months (from the date selected by the user, eg. 2021-03-01 would show all data between 2020-03-02 and 2021-03-01

Thanks again for your help

amitchandak
Super User
Super User

@PowerBI_Newb , With help from a date table, you can get rolling like

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

In case you are looking for last 12 month of display of data based on selected date

https://www.youtube.com/watch?v=duMSovyosXE

Hi Amitchandak - that's perfect! it gets the rolling data I need (thank you - not sure why I couldn't get that working last time)
I created a new measure with your formula:

_Rolling 12 = CALCULATE(sum('Sales Data Sample'[ Sales Amount ]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-12,MONTH))


Amitchandak , how can I get the chart X-axis labels to now display the years as YYYY-MM similar to the chart below:

PowerBI_Newb_0-1612151798903.png

 

@PowerBI_Newb , there are two-part to it

YYYY-MM

format([Date], "YYYYMM") should be column or sort column (if you have YYYYM)

 

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

Or do you need only on month per year ?

Please forgive me, I really am a complete newbie here...

Ok, so here's what I've done so far:


1. created a calculated column in the Calendar table with the following:
YYYY-MM = FORMAT('Calendar'[Date], "YYYYMM")

2. I've also created a measure in the 'Sales Data Sample' table:
_Rolling 12 = CALCULATE(sum('Sales Data Sample'[ Sales Amount ]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-12,MONTH))

 

3. I've created a filter (which I'll later turn into a slicer for the user) to filter out 'Calendar'[Date] with dates between 1-Jan-2015 and 1-Apr-2021

 

4. I've created a chart with _Rolling 12 in the values and Year in the Axis, which looks like this:

PowerBI_Newb_0-1612157263299.png

 



Because the end date selected was '1-Apr-2021' I'd like to get the chart to display the end date for each bar like below:
NOTE: I've updated it to display YYYY-MM-DD, based on recommendation (since YYYY-MM might confuse users)

PowerBI_Newb_1-1612157276568.png

 

 

NOTE: This is what I want --^

QUESTION: The data showing is correct. What I now want is to know what measures or columns do I need to create to change the chart from displaying YYYY to now displaying YYYY-MM-DD as shown above? (using the end date selected in the filter + making sure each bar contains a full 12 months of data - as currently shown)

 

NOTE: When I use the 'YYYY-MM' calculated column above, instead of 'Year' it gives me all the individual months (instead of the sum of the previous 12 months)

PowerBI_Newb_2-1612157287020.png

 


Many thanks again for your help 🙂

 

@PowerBI_Newb , if you have date, rolling 12 formula should give 12 months of rolling.

 

else you can have month rank on YYYYPP /YYYYMM

Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)

 

 

rolling 12  CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]<=max('Date'[Month Rank]) && ,'Date'[Month Rank]>=max('Date'[Month Rank]) -12 ))

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.