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 o...
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