Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
@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
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.
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