Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Fiscal Year and Period

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!

 

 

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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