Forum Discussion

amikm's avatar
amikm
Icon for Helper V rankHelper V
3 years ago

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