Forum Discussion
Sort the X-axis based on Fiscal year for two countries
I have a line chart that displays data for US countries whose fiscal year starts from Jan 2023 (FiscalMonthNumber 1, FiscalPeriod P1), Now I need to add logic for Non-US countries as well whose Fiscal Period Starts in Dec 2022 ( FiscalMonthNumber 1, FiscalPeriod P1)
My same graph should display data like below-
So, for the US my X-axis should be sorted like below ( Starting from the last closed month, say March 2023)
P3, P2, P1, P12, P11, P10, P9, P8, P7, P6, P5, P4
For Non-US my X-axis should be sorted like below
P4,P3, P2, P1, P12, P11, P10, P9, P8, P7, P6, P5
I have a FiscalCalendar file that has data from several years
The fiscal calendar File is uploaded here
https://drive.google.com/file/d/1i-xMqNpKrjgR1izPuq9EhF05qm3ze7gU/view?usp=sharing
My challenge is I am not able to sort the Fiscal period based on FiscalMonthNumber as values are not distinct in FiscalMonthNumber, It is 1-12 for US and 1-12 for Non-US.
At a time I can only sort FiscalPeriod for a single country ( US) by using the below DAX, but I need to satisfy both Us and Non-US country sorting based on some column
Sort=IF(FiscalCalendar[FiscalMonthNumber]= MONTH(TODAY())-1, 0, IF( FiscalCalendar[FiscalMonthNumber]> MONTH(TODAY())-1,12-(FiscalCalendar[FiscalMonthNumber]-MONTH(TODAY())-1),(MONTH(TODAY())-1)-FiscalCalendar[FiscalMonthNumber]))
I also tried to create a calculated column to provide uniqueness for the column so that I can sort data, but It is giving the same error we can't sort FiscalPeriod as values are not distinct
2 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- amikm
Helper V
lbendlin , I have uploaded the sample file to drive
https://drive.google.com/file/d/1i-xMqNpKrjgR1izPuq9EhF05qm3ze7gU/view?usp=sharing
Also, I have shared my expected output for X-axis
For the US X-axis should be sorted like below ( Starting from the last closed month, say March 2023)
P3, P2, P1, P12, P11, P10, P9, P8, P7, P6, P5, P4
For Non-US my X-axis should be sorted like below
P4,P3, P2, P1, P12, P11, P10, P9, P8, P7, P6, P5
On Y-axis you can can kept nay Measures say SalesAmount. I have provided the Fiscal Calendar that has data for both US and Non-US countries and their dates and Fiscal year , Fiscal Month and Fiscal Period etc