Is there a way to create a fiscal year column from an autocalendar colum

[Anonymous]

PowerBI newbie here with just enough tech savvy to be dangerous.

Why isn't there an easy way to create a quarter or fiscal year column from a date column?  It seems odd that it's so difficult.  To may things even more fun, our fiscal runs April-March.

I created a "Date" table with a Date column using CALENDARAUTO

Feeling very stupid 😞

[Anonymous]

I was playing around with solutions from the interwebz and the Quarter column calculation code I purloined for the desktop table (nomenclature possibly incorrect) is:

Quarter =

SWITCH(TRUE(),

'Date'[Date].[MonthNo] = 1, "Q4",

'Date'[Date].[MonthNo] = 2, "Q4",

'Date'[Date].[MonthNo] = 3, "Q4",

'Date'[Date].[MonthNo] = 4, "Q1",

'Date'[Date].[MonthNo] = 5, "Q1",

'Date'[Date].[MonthNo] = 6, "Q1",

'Date'[Date].[MonthNo] = 7, "Q2",

'Date'[Date].[MonthNo] = 8, "Q2",

'Date'[Date].[MonthNo] = 9, "Q2",

'Date'[Date].[MonthNo] = 10, "Q3",

'Date'[Date].[MonthNo] = 11, "Q3",

'Date'[Date].[MonthNo] = 12, "Q3",

BLANK())

I think I'm doing everything wrong.  There are so many ways to skin a cat with PBI and it's obviously so powerful I'm way stoked.  I appreciate your patience.

[BA_Pete · Accepted solution]

@Anonymous ,

Cool, easy one to fix:

Each segment marked with // in my code I sent you is a separate column, so your first column would be:

Date.Year([Date]+#duration(275,0,0,0))

call this finYear (so the following columns that reference it are looking for the right name).

Then your next column would be:

[finYear] - Date.Year(Date.From(DateTime.LocalNow())+#duration(275,0,0,0))

And so on, in the order that I wrote them in the code.

I'll tell you what, create a new blank query in Power Query, then with that new query selected, go to the Home tab and select Advanced Editor. Copy all of the following code and paste it over everything in the Advanced Editor window. It's all the financial dimensions for an April-March financial year starting from the beginning of the financial year 20 years ago, until the end of the financial year 2 years from now.

let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source = { Number.From(#date(Date.Year(Date.AddYears(Date.Today+#duration(275,0,0,0),-20)),4,1))..Number.From(#date(Date.Year(Date.AddYears
(Date.Today+#duration(275,0,0,0),2)),3,31)) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
  addFinYear = Table.AddColumn(renCols, "finYear", each Date.Year([date]+#duration(275,0,0,0))),
  addFinYearText = Table.AddColumn(addFinYear, "finYearText", each Text.From([finYear]-1) & "/" & Text.End(Text.From([finYear]), 2)),
  addRelativeFY = Table.AddColumn(addFinYearText, "relativeFY", each [finYear] - Date.Year(Date.Today+#duration(275,0,0,0))),
  addFinPeriod = Table.AddColumn(addRelativeFY, "finPeriod", each if Date.Month([date]) >=4 then Date.Month([date])-3 else Date.Month([date])+9),
  addFinYearPeriod = Table.AddColumn(addFinPeriod, "finYearPeriod", each [finYear]*100+[finPeriod]),
  addFinWeekKey = Table.AddColumn(addFinYearPeriod, "finWeekKey", each Date.WeekOfYear(Date.AddWeeks([date], -13), Day.Monday)),
  addFinHY = Table.AddColumn(addFinWeekKey, "finHY", each if [finPeriod] >= 1 and [finPeriod] <= 6 then "H1" else "H2"),
  addFinQtr = Table.AddColumn(addFinHY, "finQtr", each if [finPeriod] >= 1 and [finPeriod] <= 3 then "Q1"
else if [finPeriod] >= 4 and [finPeriod] <= 6 then "Q2"
else if [finPeriod] >= 7 and [finPeriod] <= 9 then "Q3"
else "Q4"),
  chgTypes = Table.TransformColumnTypes(addFinQtr, {{"finYear", type text}, {"finYearText", type text}, {"finYearPeriod", type text}, {"finPeriod", type text}, {"finWeekKey", type text}, {"finHY", type text}, {"finQtr", type text}, {"relativeFY", Int64.Type}})
in
  chgTypes

Let me know how you get on.

Pete

[BeachMom]

I am still working on getting the FY since our FY starts in October, but this worked for the Quarter: 
"Q" & CHOOSE(MONTH([Finish]),2,2,2,3,3,3,4,4,4,1,1,1)

Notice that there are 12 numbers separated by commans, each number reflects each month. In my example, the last three 1's reflect the first quarters being from October through December but if your quarter starts in a different month then adjust the numbers to reflect that.

[CLTJSDUDE82]

@BA_Pete Hi, can you tell me what I have to revise to get change it to July - June Fiscal year? thank you so much

[BA_Pete]

Hi @CLTJSDUDE82 ,

Try this:

Date.Year([date]+#duration(184,0,0,0))

Pete

[CLTJSDUDE82]

Thanks, @BA_Pete .I did some trial and error and found "275" works for June to July. 

{ (Date.Today+#duration(275,0,0,0),-10)),7,1))

However can you help me with the the week number, I can't get the first day of June to be week 1 no matter how what number I put into the Addweek function. 

Date.WeekOfYear(Date.AddWeeks([date], -26), Day.Monday)),

thanks in advance!!

[BA_Pete]

Hi @CLTJSDUDE82 ,

The code line (Source) you're making the duration edit to doesn't actually need the duration change at all. It just ensures that when you put in the "-10" for Date.AddYears it goes back 10 financial years, not calendar years.

You can simplify the Source section to just this:

let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source =
    {
      Number.From(#date(Date.Year(Date.AddYears(Date.Today, -20)), 7, 1))
      ..Number.From(#date(Date.Year(Date.AddYears(Date.Today, 2)), 6, 30))
    },

The +duration is used when you want to create a Fiscal Year column, to ensure the fiscal year increases on the correct day each year.

Regarding fiscal weeks, this gets particularly complicated due to 53/54-week years etc. As such, it's not something I'm going to get into here. My recommendation would generally be to just add a week-commencing or week-ending date in your calendar table and be done with it.

If it's something you really want/need to be able to do, then I think there's quite a few blogs on it, but they almost all come with significant caveats.

Pete

[CLTJSDUDE82]

Thanks, @BA_Pete , I will skip the week function, it's not worth it and I have little use of it. 

Regarding the duration, I actually wanted the 10 years back that's why I put the -10.

However, can you explain why the  +#duration(275,0,0,0)is no longer needed? I see you remove it in your script but had it in the prior few postings. 

Date.Year([date]+#duration(184,0,0,0))

[BA_Pete]

The bit of code you referenced there was for a Fiscal Year column (Jul-Jun) in a calendar table, so the fiscal year will increase on the correct date:

The duration isn't strictly necessary in the source line of the calendar table as you're declaring the actual date you want it to start and finish here and here:

The duration would just change the number of years forward or back to fiscal years, not calendar, so it's broadly fluff.

Pete

[CLTJSDUDE82]

@BA_Pete , Thanks this was really helpful.

So I guess I can remove it and not affect anything?

Source =

          {

            Number.From(#date(Date.Year(Date.AddYears(Date.Today,-10)),7,1))

            ..Number.From(#date(Date.Year(Date.AddYears(Date.Today,2)),6,30))

            },

[BA_Pete]

Essentially, yes. It won't affect the actual running of the code. It will just affect how many financial years you actually get when you enter '-10' and '2'. You may need to adjust this range to ensure you get the correct amount of FY's even when the calendar year increases on 1st January.

Pete

[Anonymous]

I am sending you a virtual hug (wearing appropriate virtual PPE).  I thank you so much for your help!  URGR8!

[BA_Pete]

Also, if you want to change the date range of this calendar, you just need to change these two numbers, which control the number of years before/after today respectively to run the calendar from:

Pete

[BA_Pete]

Hi @Anonymous ,

Firstly, I'd strongly recommend that you build your calendar table in Power Query. You can stick it in a dataflow and have it always available to all reports without having to materialise it in memory at runtime.

With that in mind, here's how you would create fiscal periods for your financial year in Power Query:

//Financial year (1st April-31st March):
Date.Year([date]+#duration(275,0,0,0))

//Relative financial year:
[finYear] - Date.Year(Date.From(DateTime.LocalNow())+#duration(275,0,0,0))

//Financial period:
if Date.Month([date]) >=4 then Date.Month([date])-3 else Date.Month([date])+9

//Financial half year
if [finPeriod] >= 1 and [finPeriod] <= 6 then "H1" else "H2"

//Financial quarter
if [finPeriod] >= 1 and [finPeriod] <= 3 then "Q1"
else if [finPeriod] >= 4 and [finPeriod] <= 6 then "Q2"
else if [finPeriod] >= 7 and [finPeriod] <= 9 then "Q3"
else "Q4"

//Financial yearPeriod in 202105 format:
[finYear]*100+[finPeriod]

Pete

*EDIT* Kudos given for "just enough tech savvy to be dangerous". 😂

[Anonymous]

Thanks!  Am old lady, 14 months from retirement and they give me this project?  Am nerdwannabe.

I'm getting a "Token Eof expected" error in Power Query when I pasted the code you so generously (and with alacrity) provided.  It doesn't like the "if" in the "Financial Period" line.

[BA_Pete]

@Anonymous ,

No worries. Did the Financial Year one work ok?

If you've not tried it, then I expect that it is the [date] part that is causing problems. Power Query is entirely case-sensitive so, if your date field is [Date], it won't like it.

If changing the [date] field to the correct name/capitalisation doesn't work, then can you post a screenshot of your new custom column code window showing the code and the error please?

Pete