Fiscal Year and Period

[Anonymous]

Hello, 

I am trying to create new columns for the Fiscal Period on a few different dates with Power Query:

Our Fiscal Year starts on April 01st, so Period 1 is April -  P01 ; May P02 and so on.

I have a different table called Calendar where for each possible date, there are all the possible formats, including the Fiscal year, Fiscal period ( P01, Period 1, Period 1 - April, etc.) but the relationship can only be with one column (date), right?

I am tryring to find a formula where I can calculate the period and the result will be 3 caracter including a "P" for period (e.i.: P01, P02, [...], P10, P11, P12) and also the Fiscal Year : 2021-2022 or FY2021-2022.

Thank you!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

In Power Query, for an Apr 1st - May 31st financial year, you can use the following:

// Financial year "2021"
Date.Year([date]+#duration(275,0,0,0))

// Financial year "2020-21"
Text.From([finYear]-1) & "/" & Text.End(Text.From([finYear]), 2)

// Financial period "1", "2" etc.
if Date.Month([date]) >=4
then Date.Month([date])-3
else Date.Month([date])+9

// Financial period "P01", "P02" etc.
if Date.Month([date]) >=4
then "P" & Text.PadStart(Text.From(Date.Month([date])-3), 2, "0")
else "P" & Text.PadStart(Text.From(Date.Month([date])+9), 2, "0")

Pete

[Vijay_A_Verma]

Need to look at some sample data to work out the steps for you. Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.