topic Re: Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year in DAX Commands and Tips

Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year

chanal — Sun, 12 Jul 2020 21:20:09 GMT

Hello,

Please help : I would like to sorth month depending on 2 criterias :

1- the beginning of fiscal year

2-the end of fiscal year

a fiscal year can last more than 12 months.

example :

1-If fiscal year is between 01/10/2018-30/09/19 => i would like the 1st month order of booking date begins in october, and next month november as number 2

2-if fiscal year is between 01/10/19-31/12/2020 => i would like the 1st month order begins in october, but last month december as number 15 and no more 12

3-if fiscal year is between 01/01/2021-31/12/2021 => 1st month january and 12 months

Thanks !

Re: Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year

amitchandak — Mon, 13 Jul 2020 05:01:14 GMT

chanal , The values witch you are showing is not possible, like in the second set in yellow/orange you have two oct with different sort values. Not possible to do that.

You can have a custom year and sort custom month start date.

Re: Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year

chanal — Mon, 13 Jul 2020 08:03:23 GMT

Hello,

Due to the answer of amitchandak , I have changed my resquest and make it simplier.

I have removed the case when fiscal year is more than 12 month.

Here is my new example please if you can answer :

1- When Fiscal year or Custom Year begins in this example 01/10/18-30/09/2019, and when the booking date is october, then the sort order must begin in october as number 1.

2-the beginning of the Fiscal or Custom Year can change => it can be 01/01/19-31/12/19, or 01/07/19-30/06/20, etc.. but they all last 12 months

Thank you 🙂

Re: Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year

amitchandak — Mon, 13 Jul 2020 08:17:09 GMT

chanal , I have this blogpost , where you can get link for the calendar that can start for any of the 12 months with month sort order.

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

Like the post, if that help in solving your problem

Re: Sort month depending on 2 criterias : beginning of fiscal year and the end of fiscal year

chanal — Mon, 13 Jul 2020 09:23:26 GMT

hI amitchandak

Wonderful ! Thank you so much !! great blogs of yours !