Extended Date table Fiscal Period Offset

Power Query M Code Showcase
1

Probably one for @Melissa, this one.

I’m trying to set up a Fiscal Period offset. The normal extended date table doesn’t require this because it works on calendar months and the month offset is the same. However, I am in the middle of setting up a 445 table in SQL and PQ and the Fiscal Period offset will be different from the month offset.

I have something which works but there might be a smarter way to do this.

My approach is to set up an indexed table with FPeriodnYear, IsCurrentFP.

let
    Source = #"Date Generator"(Date.AddYears(CalendarStart,-2), BudgetYearEnd, FiscYearStartMonth, OfficialHolidays[OfficialHoliday], null),
    #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(Source,{"FPeriodnYear", "IsCurrentFP"})),
    #"Indexed FPnYear" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1, Int64.Type)
in
    #"Indexed FPnYear"

Then reference this and find current year index

    Source = AllFIscalPeriodnYearIndex,
    #"Filtered Rows" = Table.SelectRows(Source, each ([IsCurrentFP] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"FPeriodnYear", "IsCurrentFP"}),
    Index = #"Removed Columns"{0}[Index]
in
    Index
type or paste code here

Merge this with the main date table and perform the subtraction.

This does work but there may be a more optimal way to do this in the date function?

Pete

2

Check if you can follow Matt Allington’s approach.

3

HI @pranamg

I’ve looked at Matt’s solution and by his own admittance he said it doesn’t handle 53 week years at time of posting.

I’m actually writing a flexible calendar both in SQL and PQ. Then depending on client’s set up, I’ll apply the appropriate calendar. I’m doing this with a fiscal calendar, with the fiscal cols being updated with a separate 445 calendar when necessary. The end job will have the relevant 445 cols update the fiscal columns in Melissa’s table. Then I have a flexible calendar for most scenarios.

The SQL piece is almost done and plan to share here once fully tested. Need to talk with @BrianJ later and ask how best to share the scripts on the forum.

My inspiration for the PQ 445 calendar has come from https://hoosierbi.com/2021/07/04/445-calendar-with-53-week-years/. Plan to put hands to keyboard on that one in a couple of weeks.

Question still remains though - was my PQ approach to Fisc Period offset OK or is there a smarter way?

Pete

4

Hi @BINavPete! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!