Fiscal year/quarter calculations

I have been doing some work in Microsoft PowerApps recently and found that as part of one of the application requirements it was necessary to calculate for the user the current fiscal year (e.g. FY19) as well as the current fiscal quarter (e.g. Q1, Q2, Q3 or Q4). This information was then used to pre-populate the relevant fields in the data entry form, disabled so they can’t be changed.

Here are the expressions I use in the Default field for the relevant text boxes.

Fiscal Year
If(Month(Today())>6,"FY"&Right(Text(Year(Today())+1),2),"FY"&Right(Text(Year(Today())),2))
Fiscal Quarter
"Q" + RoundUp((If(Month(Today()) >= 7 , Month(Today()) - 6,Month(Today()) + 6 )/3),0)

Not long after this was implemented the requirement was change so that the app should be setting these values based on what they would be for the following month. For example normally June this would be FY18 and Q4 but with this change they should instead by FY19 and Q1. This means September will still be FY19 but Q2 instead of Q1.

Here are the revised expressions I use in the Default field for the relevant text boxes.

Fiscal Year a Month in Advance
If(Month(Today())>=6,"FY"&Right(Text(Year(Today())+1),2),"FY"&Right(Text(Year(Today())),2))
Fiscal Quarter a Month in Advance
"Q" & RoundUp((If(Month(Today())+1 >= 7  , Month(Today()) - 5,Month(Today()) + 7 )/3),0)
Fiscal Year a Fiscal Quarter in Advance
If(Month(Today())>=4,"FY"&Right(Text(Year(Today())+1),2),"FY"&Right(Text(Year(Today())),2))
Fiscal Quarter a Fiscal Quarter in Advance
"Q" & RoundUp ((If( Month(Today())+3 >= 7  , Month(Today()) - 3,Month(Today()) + 9 )/3),0)
Advertisements

Leave a comment

Filed under PowerApps, SharePoint Online, Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s