Date column with custom start and end dates

[Class66Loco]

Hello,

I created a date column using the below formula but after 31 December the next date is 1 April.

UK Fiscal Date Calendar =CALENDAR(DATE(2023,4,6),DATE(2024,4,5))

Can someone please advise where I'm going wrong? I'm trying to have a date column that starts on 6 April and ends 5 April.

The formula I tried works but after 31 December it should go to 1 January etc and finish on 5 April.

Thanks

[amitchandak]

@Class66Loco , This should work, you might have sort on the column to check the correct order

UK Fiscal Date Calendar =CALENDAR(DATE(2023,4,6),DATE(2024,4,5))

But for the fiscal calendar change

Calendar that starts with any date of the month — Just two variables apart
https://medium.com/chandakamit/cheat-sheet-calendar-that-starts-with-any-date-of-the-month-just-two-...

others

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

[Class66Loco]

Thanks. I've sort of figured out my error. I had other columns connected to it and these didn't display the month number until I adjusted something. 

My only remaining issue I hope is trying to fix a Fiscal Month column.
FiscalMonth = (If( Month([Date]) >= 4 , Month([Date]) - 3,Month([Date]) + 9 ))

The formula works except for April 1 to April 5 where it allocates it a 1 instead of a 12. Its rightly following the logic but I'm trying to find a way to make those 5 days show as Fiscal Month 12 instead.

[v-yiruan-msft · Accepted solution]

Hi @Class66Loco ,

You can update the formula of calculated column [FiscalMonth] as below to get the correct values:

FiscalMonth = 
IF (
    MONTH([Date]) > 4 || (MONTH([Date]) = 4 && DAY([Date]) > 5),
    MONTH([Date]) - 3,
    IF (
        MONTH([Date]) = 4 && DAY([Date]) <= 5,
        12,
        MONTH([Date]) + 9
    )
)

Best Regards