Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I'm currently working with an extensive dataset to generate a number of charts and graphs based on dates. However, we would like to represent these in a fiscal year order (i.e. July to June, rather than January to december). Since January, we are encountering the issue below, where data for january and february are listed before july of the year before, and we do not succeed in changing this.
I haven't been able to reformat the dates in the table to something that would sort on year rather than over month except for drilling down to 'year', but that would mean we cannot view monthly evolutions.
Is there any way I can change the internal calendar calculation of Power Bi, or do I have to create a new measure/column and change all graphs? (in the second case: any hints on a successful formula? I have tried some that I found on the internet, but without succes. My dataset represents dates as dd/mm/yyyy, which is automatically changed by Power Bi into eg. "Friday, 22 december 2017"
Thanks in advance!
Boris
Solved! Go to Solution.
If I understood correctly, you want to see the X-Axis from July to June.
I considered from june:
Here I go to the query editor, and created a Month column and created a conditional column based on the Month. Here is the M code.
= Table.AddColumn(#"Renamed Columns1", "Custom", each if [#"Month-Number"] = 1 then 8
else if [#"Month-Number"] = 2 then 9
else if [#"Month-Number"] = 3 then 10
else if [#"Month-Number"] = 4 then 11
else if [#"Month-Number"] = 5 then 12
else if [#"Month-Number"] = 6 then 1
else if [#"Month-Number"] = 7 then 2
else if [#"Month-Number"] = 8 then 3
else if [#"Month-Number"] = 9 then 4
else if [#"Month-Number"] = 10 then 5
else if [#"Month-Number"] = 11 then 6
else if [#"Month-Number"] = 12 then 7
else null)
and change the Custom Column format to Number.
Now, you select the Date and then goto Modeling Tab and then select sort by Custom column.
If I understood correctly, you want to see the X-Axis from July to June.
I considered from june:
Here I go to the query editor, and created a Month column and created a conditional column based on the Month. Here is the M code.
= Table.AddColumn(#"Renamed Columns1", "Custom", each if [#"Month-Number"] = 1 then 8
else if [#"Month-Number"] = 2 then 9
else if [#"Month-Number"] = 3 then 10
else if [#"Month-Number"] = 4 then 11
else if [#"Month-Number"] = 5 then 12
else if [#"Month-Number"] = 6 then 1
else if [#"Month-Number"] = 7 then 2
else if [#"Month-Number"] = 8 then 3
else if [#"Month-Number"] = 9 then 4
else if [#"Month-Number"] = 10 then 5
else if [#"Month-Number"] = 11 then 6
else if [#"Month-Number"] = 12 then 7
else null)
and change the Custom Column format to Number.
Now, you select the Date and then goto Modeling Tab and then select sort by Custom column.
Thanks! This does the trick!
I have duplicated my date column and selected to show it as month in numbers, then I created the conditional column as you suggested, For clarity's sake, I then changed the output dates as followed "1 (Jul'17)", if it resembles some kind of date notation too much, Power BI will automatically change it to date format, and the sorting is altered. If I just use "1, 2, 3", it's kind of hard to know which month I'm talking about. I haven't sorted my original date column with the fiscal month number, as the workaround above seemed sufficient. (I do not need to drill through to actual days.)
The tables look like this now:
Thanks for your help! Until we can change the internal calendar of Power BI, this is for me the workaround involving the least of complicated formulas and calculations.
glad you have sorted it out. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |