Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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.
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
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
Do you want this?!
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:
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
@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:
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 , 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:
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)
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)
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 ))
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |