topic Re: Custom Calendar fiscal year october in DAX Commands and Tips

Custom Calendar fiscal year october

MQUINT-07 — Wed, 06 Jul 2022 17:06:58 GMT

i want to create a day number on DAX Calendar.

we have a custom calendar for fiscal year, our fiscal year stars on october 1 and i want to create a column when day #1 are october first, 2 october second...

could you please help?, i alredy try trhu date dif:

daynum#FIX = DATEDIFF ( DATE ( YEAR ('Calendario End Date'[Date]),10,1 ), 'Calendario End Date'[Date], DAY ) + 1

but when function comes to 1jan retun -273

thanks in advance for the help

Re: Custom Calendar fiscal year october

technolog — Wed, 06 Jul 2022 17:13:37 GMT

You need to change number of YEAR

In your case you get current YEAR number but you need to use DATEADD and move start of the year.

You need to try something like this:

daynum#FIX = DATEDIFF ( DATE ( DATEADD( YEAR ('Calendario End Date'[Date]),10,1 ) , XXX, DAY), 'Calendario End Date'[Date], DAY ) + 1

where XXX - the number of days that you change you from calendar year.

Re: Custom Calendar fiscal year october

MQUINT-07 — Wed, 06 Jul 2022 18:03:10 GMT

i trayed but its still remarkme error,

daynum#FIX2 = DATEDIFF (DATEADD('Calendario End Date',273,DAY), 'Calendario End Date'[Date], DAY ) + 1

thanks for your support.

best regards.

Re: Custom Calendar fiscal year october

technolog — Wed, 06 Jul 2022 19:07:14 GMT

try add

daynum#FIX2 = DATEDIFF (DATEADD('Calendario End Date'[Date],273,DAY), 'Calendario End Date'[Date], DAY ) + 1

Re: Custom Calendar fiscal year october

MQUINT-07 — Wed, 06 Jul 2022 21:27:25 GMT

Hello Thanks for your help.

its help me to find the way, the solve it´s the following:

daynum#FIX2 = IF('Calendario End Date'[Date] >= DATE(YEAR('Calendario End Date'[Date]),10,1),DATEDIFF(DATE(YEAR('Calendario End Date'[Date]),10,1),'Calendario End Date'[Date],DAY)+1,DATEDIFF (DATE(YEAR('Calendario End Date'[Date]),1,1), DATEADD('Calendario End Date'[Date],92,DAY), DAY ) + 1)

i was to use an if function to declare when it will the first day of the year (1 oct) and use 2 function 1 for dates after 1 oct and dates before.

i really apreciate your time.

best regards.

Re: Custom Calendar fiscal year october

technolog — Wed, 06 Jul 2022 21:35:06 GMT

I'm very glad that it helps you!