Change in Financial Year

DAX DAX Calculations
1

Hi Everyone, hoping someone can help me, I am pretty new to Power Bi. I’ve built some really nice reports with the help of Enterprise DNA of course :slight_smile: But I have hit a wall! My Company’s financial reporting year has changed. Previously, they had a November year end but this has changed to March. SO, our 2022 financial year has change from 01.12.21 - 30.11.22 to 01.12.21 to 31.03.23. I am trying to recalculate the FY Qtrs in my Date Table to account for the additional 4 months of this financial year…by calling them “Q5”. Previously I just used the “Switch (True)” formula to calculate the FY Qtrs which worked perfectly, below is my attempt to account for “Q5” which isn’t working, would someone be able to guide me as to where I am going wrong?

FY Quarters =
SWITCH( TRUE(),
Dates[MonthOfYear] = 1 , “Q1”,
Dates[MonthOfYear] = 2 , “Q1”,
Dates[MonthOfYear] = 3 , “Q2”,
Dates[MonthOfYear] = 4 ,“Q2”,
Dates[MonthOfYear] = 5 , “Q2”,
Dates[MonthOfYear] = 6 , “Q3”,
Dates[MonthOfYear] = 7 , “Q3”,
Dates[MonthOfYear] = 8 , “Q3”,
Dates[MonthOfYear] = 9 , “Q4”,
Dates[MonthOfYear] = 10 , “Q4”,
Dates[MonthOfYear] = 11 , “Q4”,
Dates[MonthOfYear] = 12 , “Q1”,
IF(Dates[Year] = “2022”,
SWITCH( TRUE(),
Dates[MonthOfYear] = 12, “Q5”,
IF( Dates[Year] = “2023”,
SWITCH( TRUE (),
Dates[MonthOfYear] = 1, “Q5”,
Dates[MonthOfYear] = 2, “Q5”,
Dates[MonthOfYear] = 3, “Q5”,
BLANK() )))))

Many thanks
Melissa

2

Hi Melissa,
Have you considered using a Calendar Table from Power Query rather than addressing this in DAX?

You can create 3 Financial Year Calendars in Power Query.

  1. until FY21 with old quarters.
  2. only for FY22 with 5 quarters to account for the change in start of FY
  3. for FY23 onward.

You can then append these 3 tables into a single Calendar Table in Power Query.

3

Hi Pranamg,

No I have not!

Will definitely give this a try! Thank you for your help!

Kind regards
Melissa

4

Hello @melissavn13

Did the response from @pranamg help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

5

Hi @melissavn13 ,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks to @pranamg for contributing to this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!