cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
russrimm
Microsoft
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
RezaDorrani
Dual Super User II
Dual Super User II

Hi @russrimm 

 

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

 

RusselThomas
Microsoft
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

 

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

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)