Forum Discussion
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...
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
BA_Pete
Super User
Super UserHi 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