cancel
Showing results for
Did you mean:
Microsoft

Fiscal Year Switch or If

Hi all,

I've created a monster and it's set up for eventual failure if I don't continue updating it to support the rest of the future or figure out a way to make it always handle fiscal year calculations without making it 1000 pages of IFs.  Even if I use SWITCH to clean it up I can't think of way to solve this one (make it forever work with a few lines of code).

If(Year(EndDatePicked.SelectedDate) = 2019 && Month(EndDatePicked.SelectedDate) >= 7 && Month(EndDatePicked.SelectedDate) <= 9,UpdateContext({Quart:1,Yr:20})) ;
If(Year(EndDatePicked.SelectedDate) = 2019 && Month(EndDatePicked.SelectedDate) >= 10 && Month(EndDatePicked.SelectedDate) <= 12,UpdateContext({Quart:2,Yr:20})) ;
If(Year(EndDatePicked.SelectedDate) = 2020 && Month(EndDatePicked.SelectedDate) >= 1 && Month(EndDatePicked.SelectedDate) <= 3,UpdateContext({Quart:3,Yr:20})) ;
If(Year(EndDatePicked.SelectedDate) = 2020 && Month(EndDatePicked.SelectedDate) >= 4 && Month(EndDatePicked.SelectedDate) <= 6,UpdateContext({Quart:4,Yr:20})) ;
If(Year(EndDatePicked.SelectedDate) = 2020 && Month(EndDatePicked.SelectedDate) >= 7 && Month(EndDatePicked.SelectedDate) <= 9,UpdateContext({Quart:1,Yr:21})) ;
If(Year(EndDatePicked.SelectedDate) = 2020 && Month(EndDatePicked.SelectedDate) >= 10 && Month(EndDatePicked.SelectedDate) <= 12,UpdateContext({Quart:2,Yr:21})) ;
If(Year(EndDatePicked.SelectedDate) = 2021 && Month(EndDatePicked.SelectedDate) >= 1 && Month(EndDatePicked.SelectedDate) <= 3,UpdateContext({Quart:3,Yr:21})) ;
If(Year(EndDatePicked.SelectedDate) = 2021 && Month(EndDatePicked.SelectedDate) >= 4 && Month(EndDatePicked.SelectedDate) <= 6,UpdateContext({Quart:4,Yr:21})) ;
If(Year(EndDatePicked.SelectedDate) = 2021 && Month(EndDatePicked.SelectedDate) >= 7 && Month(EndDatePicked.SelectedDate) <= 9,UpdateContext({Quart:1,Yr:22})) ;
If(Year(EndDatePicked.SelectedDate) = 2021 && Month(EndDatePicked.SelectedDate) >= 10 && Month(EndDatePicked.SelectedDate) <= 12,UpdateContext({Quart:2,Yr:22})) ;
If(Year(EndDatePicked.SelectedDate) = 2022 && Month(EndDatePicked.SelectedDate) >= 1 && Month(EndDatePicked.SelectedDate) <= 3,UpdateContext({Quart:3,Yr:22})) ;
If(Year(EndDatePicked.SelectedDate) = 2022 && Month(EndDatePicked.SelectedDate) >= 4 && Month(EndDatePicked.SelectedDate) <= 6,UpdateContext({Quart:4,Yr:22})) ;
If(Year(EndDatePicked.SelectedDate) = 2022 && Month(EndDatePicked.SelectedDate) >= 7 && Month(EndDatePicked.SelectedDate) <= 9,UpdateContext({Quart:1,Yr:23})) ;
If(Year(EndDatePicked.SelectedDate) = 2022 && Month(EndDatePicked.SelectedDate) >= 10 && Month(EndDatePicked.SelectedDate) <= 12,UpdateContext({Quart:2,Yr:23})) ;

3 REPLIES 3
Dual Super User II

Not sure if you need all this

RoundUp(
Month(EndDatePicked.SelectedDate) / 3,
0
)

will give you the current quarter

Year(EndDatePicked.SelectedDate)

will give you the year

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Microsoft

Hi @russrimm ,

It might be useful to create a static reference table in your app with all the months of the year and related quarters (without the year, so basically a 12 row, two column table).

You can create it easily enough in Excel and then import the Excel table into the app (not connected, but imported once-off) - or just create a collection manually at startup.

Call it something like "tblDateReference";

Month            Quarter

 1 3 2 3 3 3 4 4 5 4 6 4 7 1 8 1 9 1 10 2 11 2 12 2

You can then use the EndDatePicked.SelectedDate to LookUp the quarter and dynamically indicate the Financial Year based on the current Quarter.

So:

```UpdateContext({
Quart:  LookUp(tblDateReference, Month=Month(EndDatePicked.SelectedDate), Quarter), //self explanatory
Yr: If(LookUp(tblDateReference, Month=Month(EndDatePicked.SelectedDate), Quarter) < 3, //FY depends on the Quarter                Year(EndDatePicked.SelectedDate) + 1,  //if Q is 1 or 2, then the FY is the selected Year + 1                Year(EndDatePicked.SelectedDate)       // if the Q is 3 or 4, then the FY is the selected Year             )
}))```

It should also work in perpetuity without requiring formula updates as the years roll by.

Hope this helps,

RT

Microsoft

Thanks Reza.  The app allows you to pick dates in the future and I need to calculate the quarter and fiscal year based on the date picked.  When I try your ROUND and pick 9/20/2019 it says 3, but July-Sept 2019 is Q1 FY20.  The fiscal is July to June.

Thanks!

Announcements

Launching new user group features

Learn how to create your own user groups today!

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,308)