Custom Fiscal Year Calendar

[dkay84_PowerBI]

This is not for the faint of heart but I'm hoping to crowdsource a solution as I work on it myself.  Here is the description of the calendar I need to model in PowerQuery/Query Editor:

The Company's Fiscal Year begins with the week containing February 1. This doesn't mean the February 1 is the beginning of their fiscal year. It means that the week CONTAINING February 1 marks the beginning of the year.

The Company's weeks start on Saturday at 12:01 in the morning and run through Friday at midnight (my data doesn't go to time level, only day, so don't worry about time of day).  So, for the beginning of the fiscal year, we first look for the week containing February 1, then find the preceding Saturday, which will then be the beginning of the fiscal year. 

The Company divides its quarters up by a 4-5-4 method. This means that Quarter 1 of any year consists of February, March and April. No matter what year, this always holds true. So the 4-5-4 rule means that the first month of every quarter has 4 weeks, second month has 5 weeks, and the third quarter has 4 weeks. This then repeats for each successive quarter. I have some experience building out a 4-5-4 calendar in PowerQuery but using a static date as the start of the Fiscal Year, not like this situation.

Any help is appreciated.

[dkay84_PowerBI · Accepted solution]

For anyone who is interested, I was able to adapt the approach in this blog post to solve my calendar needs.  Take a look if you're interested in the solution:

Date Dimension PBIX

[Anonymous]

Hi, 

I know this is an old post, but hopefully you will see this:

I have a fiscal date table setup in my PowerBI. I'm trying to do Y/Y calculations with the SAMEPERIODLASTYEAR function. The function itself works, but, given that our fiscal year doesn't start on the exact same day every year, I'm running into an issue.

I.e., FY21-Q1 began on February 1st, 2020 whereas FY22-Q1 began on January 30th, 2021. For my Y/Y calculations, PowerBI obviously doesn't consider this... So, PowerBI compares January 30th, 2021 to January 30th, 2020 rather than February 1st, 2020. Instead of comparing exact dates to exact dates, I want to compare Day 1 of Week 1 of Quarter 1 of FY21 to Day 1 of Week 1 of Quarter 1 of FY20... Does this make sense?

Any ideas?

[Anonymous]

@Chris99 @lee_a @AmberM @FrugalEconomist 

[Chris99]

Were you aware that the latest Feb 2018 update (2.55.5010.521) allows a custom date table to be specified? It may be worth looking at this.

Chris

[AmberM]

Thank you Chris, and yes, I did see this. My data is only listed at a fiscal week level, so the dynamic SAMEPERIODLASTYEAR calculations do not add up correctly. I'm new to Power BI, so it may be figuring out how to create with an OFFSET calc.

[Chris99]

I think you would need to hold fiscal week, month, quarter and year information in your date table.

[AmberM]

I have i built in, but the calcs don't roll up. I'm sure I'm doing something wrong!

[Chris99]

@AmberM- Probaly SAMEPERIODLASTYEAR uses ISO calendar calculations rather than fiscal or financial ones. You will probably need to roll your own equaivalent. Try Googling "dax SAMEPERIODLASTYEAR fiscal calendar".

Let me know if you have any success.

Chris

[AmberM]

Here is the calculation thanks to Enterprise DNA, but it doesn't produce totals for a table, just by week number in the table.

https://www.youtube.com/watch?v=aODroF37pq0

Previous Year Sales = 
VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
		Dates[Week Number] = CurrentWeek && Dates[Year] = CurrentYear - 1))

[dkay84_PowerBI]

So far I have (using some of my own logic as well as piecing together steps from http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/) created a custom FY calendar that accurately tracks the dynamic situation given above.  However, where I run into a problem is with the 53rd week that occurs every 6 years.  This 53rd week is added on to the end, so the last quarter is 4-5-5 in length.

The 53 week years in my Dates table are FY 2014 and 2020.  When it gets to the end of week 52 of FY 2014 (1/18/2014 - 1/24/2014) the custom columns reset at 1, but I need them to address the 53rd week issue and push all later dates columns (so week 2 of FY 2015 becomes week 1).

If someone could tell me if there is a way to attach a file to a post I will upload my PBIX.

[Vvelarde]

@dkay84_PowerBI

Upload in Drive, Dropbox, or another and post the link.

[dkay84_PowerBI · Accepted solution]

For anyone who is interested, I was able to adapt the approach in this blog post to solve my calendar needs.  Take a look if you're interested in the solution:

Date Dimension PBIX

[krishanw]

Can I get this PIBX?

[lee_a]

Thank you so much! this is exactly what I'm looking for! 

[AmberM]

How do you handle the calculations for same period last year if you want to compare for example, Fiscal Week 201701 vs 201601? Same for if you want to look at last 2 weeks, last 4 weeks vs prior year? The SAMEPERIODLASTYEAR calculation doesn't add up correctly.

Thank you for posting this!

[dkay84_PowerBI]

You will need to do some math/logic such as CALCULATE([Measure],Filter('Calendar','Calendar'[FiscalWeek]=...))

where the "..." is your offset.  For example, if your fiscal week is 201812, and you want to see same week last year, then you must subtract 100 = 201712.

There may be other ways to do this, possibly even out of the box functions too.

[Anonymous]

I've been using a similar date dimension to the one referenced about for a while now and recently had a customer who used a FY calendar starting on July 1st.  Since nothing I've seen anywhere else did what I needed, I built the following.  In the middle starting at "InsertFyYear" I've added in logic to build some specific columns for an FY Calendar.  

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
 let
 DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
 Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
 InsertYear = Table.AddColumn(RenamedColumns, "CALYear", each Date.Year([Date])),
 InsertQuarter = Table.AddColumn(InsertYear, "CALQuarterOfYear", each Date.QuarterOfYear([Date])),
 InsertMonth = Table.AddColumn(InsertQuarter, "CALMonthOfYear", each Date.Month([Date])),
 InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
 InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [CALYear] * 10000 + [CALMonthOfYear] * 100 + [DayOfMonth]),
 InsertMonthName = Table.AddColumn(InsertDayInt, "CALMonthName", each Date.ToText([Date], "MMMM", Culture), type text),
 InsertMonthShort = Table.AddColumn(InsertMonthName, "CALMonthNameShort", each Date.ToText([Date], "MMM", Culture), type text),
 InsertCalendarMonth = Table.AddColumn(InsertMonthShort, "CALMonthInCalendar", each (try(Text.Range([CALMonthName],0,3)) otherwise [CALMonthName]) & " " & Number.ToText([CALYear])),
 InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "CALQuarterInCalendar", each "Q" & Number.ToText([CALQuarterOfYear]) & " " & Number.ToText([CALYear])),
 InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
 InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
 InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

 InsertFyYear = Table.AddColumn(InsertWeekEnding, "zFyYearSort", each if [CALMonthOfYear] <= 6 then Date.Year([Date])-1 else Date.Year([Date])),
 InsertFyYearCode  = Table.AddColumn(InsertFyYear , "FyYear", each Text.End(Text.From([zFyYearSort]),2)&"/"&Text.End(Text.From([zFyYearSort]+1),2),type text),
 InsertFyQtr = Table.AddColumn(InsertFyYearCode  , "FyQuarter", each if [CALMonthOfYear] <= 6 then "Q"&Text.From(Date.QuarterOfYear([Date])+2) else "Q"&Text.From(Date.QuarterOfYear([Date])-2)),
 InsertFyMonth = Table.AddColumn(InsertFyQtr , "FyMonth", each if [CALMonthOfYear] <= 6 then Date.Month([Date])+6 else Date.Month([Date])-6),
 InsertFyYearQtr = Table.AddColumn(InsertFyMonth , "FyYearQtr", each [FyYear] & "-" & [FyQuarter], type text),
 InsertFyYearMonth = Table.AddColumn(InsertFyYearQtr , "FyYearMonth", each [FyYear] & "-" & [CALMonthNameShort], type text),
 InsertFyYearMonthNum = Table.AddColumn(InsertFyYearMonth, "FyYearMonthNum", each Text.From([zFyYearSort]) & "-" & Text.PadStart(Text.From([FyMonth]),2,"0"), type text),

 InsertCurrentThursday = Table.AddColumn(InsertFyYearMonthNum , "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
 InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
 InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOYear", each Date.Year([CurrentThursday])) ,
 InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
 if [CurrentThursday] < [ISOWeekJan4]
 then Date.AddDays([CurrentThursday],-3)
 else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
 ,type date),
 InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeek", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
 InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOYear] * 100 + [ISOWeek], type number),
 InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOYear]) & "W" & Text.End( "0" & Text.From(([ISOWeek]*10) + [DayInWeek]),3)),
 InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOYear]) & "-W" & Text.End( "0" & Text.From([ISOWeek]),2) & "-" & Text.From([DayInWeek])),

 fnPeriod445a = (weekNum) => let
 Periods =
 {
 {(x)=>x<5, [P=1,Q=1,M="Jan"]},
 {(x)=>x<9, [P=2,Q=1,M="Feb"]},
 {(x)=>x<14, [P=3,Q=1,M="Mar"]},
 {(x)=>x<18, [P=4,Q=2,M="Apr"]},
 {(x)=>x<22, [P=5,Q=2,M="May"]},
 {(x)=>x<27, [P=6,Q=2,M="Jun"]},
 {(x)=>x<31, [P=7,Q=3,M="Jul"]},
 {(x)=>x<35, [P=8,Q=3,M="Aug"]},
 {(x)=>x<40, [P=9,Q=3,M="Sep"]},
 {(x)=>x<44, [P=10,Q=4,M="Oct"]},
 {(x)=>x<48, [P=11,Q=4,M="Nov"]},
 {(x)=>true, [P=12,Q=4,M="Dec"]}
 },
 Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
 in
 Result,

 InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeek])),
 ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q","M" }, {"ISOMonth", "ISOQuarter", "ISOMonthName"}),
 RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon", "ISOWeekJan4"}),
 DaysFromToday = Table.AddColumn(RemovedColumns, "Days from Today" , each Date.From(DateTime.LocalNow()) - [Date]),
 WeeksFromToday = Table.AddColumn(DaysFromToday, "Weeks from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/7),
 MonthsFromToday = Table.AddColumn(WeeksFromToday, "Months from Today" , each (Date.From(DateTime.LocalNow()) - [Date])/(365/12)),
 ChangedType1 = Table.TransformColumnTypes(MonthsFromToday,{{"Date", type date}, {"CALYear", Int64.Type}, {"CALQuarterOfYear", Int64.Type}, {"Days from Today", Int64.Type}, {"Weeks from Today", Int64.Type}, {"Months from Today", Int64.Type}, {"CALMonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"CALMonthName", type text}, {"CALMonthNameShort", type text}, {"CALMonthInCalendar", type date}, {"CALQuarterInCalendar", type text}, {"DayInWeek", Int64.Type}, {"DayOfWeekName", type text}, {"WeekEndingFriday", type date}, {"zFyYearSort", Int64.Type},{"FyYear", type text}, {"FyQuarter", type text}, {"FyMonth", Int64.Type}, {"ISOYear", Int64.Type}, {"ISOWeek", Int64.Type}, {"ISOWeekID", Int64.Type}, {"ISOWeekName", type text}, {"ISOWeekNameLong", type text}, {"ISOMonth", Int64.Type}, {"ISOQuarter", Int64.Type}, {"ISOMonthName", type text}}),
 InFuture = Table.AddColumn(ChangedType1 , "Date in Future" , each if Number.Sign([Days from Today]) = -1 then true else false, type logical),
 ReorderedColumns = Table.ReorderColumns(InFuture ,{"Date", "CALYear", "CALQuarterOfYear", "CALMonthOfYear", "DayOfMonth", "DateInt", "CALMonthName", "CALMonthNameShort", "CALMonthInCalendar", "CALQuarterInCalendar", "DayInWeek", "DayOfWeekName", "WeekEndingFriday", "zFyYearSort", "FyYear", "FyQuarter", "FyMonth", "FyYearQtr", "FyYearMonth", "FyYearMonthNum", "ISOYear", "ISOMonth", "ISOQuarter", "ISOWeek", "ISOMonthName", "ISOWeekID", "ISOWeekName", "ISOWeekNameLong"})

 in
 ReorderedColumns ,
    #"Invoked FunctionCreateDateTable" = CreateDateTable(#date(1999, 01, 01), #date(2018, 12, 31), "en-US")
in
    #"Invoked FunctionCreateDateTable"

By the way, the ISO calendar part in this is based on a 4-4-5 calendar, if you wanted a 4-5-4 calendar, make the following changes to the Period lookup near the middle.

 Periods =
 {
 {(x)=>x<5, [P=1,Q=1,M="Jan"]},
 {(x)=>x<10, [P=2,Q=1,M="Feb"]},
 {(x)=>x<14, [P=3,Q=1,M="Mar"]},
 {(x)=>x<18, [P=4,Q=2,M="Apr"]},
 {(x)=>x<23, [P=5,Q=2,M="May"]},
 {(x)=>x<27, [P=6,Q=2,M="Jun"]},
 {(x)=>x<31, [P=7,Q=3,M="Jul"]},
 {(x)=>x<36, [P=8,Q=3,M="Aug"]},
 {(x)=>x<40, [P=9,Q=3,M="Sep"]},
 {(x)=>x<44, [P=10,Q=4,M="Oct"]},
 {(x)=>x<49, [P=11,Q=4,M="Nov"]},
 {(x)=>true, [P=12,Q=4,M="Dec"]}
 },

[drgrd13]

My company fiscal month starts from 1st Jan and ends on 31st Dec. following the pattern of 4-4-5. How to edit the calendar you have created.Also if the week ends on saturday instead of Friday

[dkay84_PowerBI · Accepted solution]

Here is the code from the Advanced Editor for those who prefer not to download the file (red text refers to paramters but can be replaced with hard coded dates or dynamic retrieval of earliest and latest dates in you Fact table).

let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)), 
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), 
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), 
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), 
    InsertDayName = Table.AddColumn(RenamedColumns, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text), 
    InsertDayWeek = Table.AddColumn(InsertDayName, "DayInWeek", each Date.DayOfWeek([Date],6)+1), 
    InsertWeekEnding = Table.AddColumn(InsertDayWeek, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),    
    InsertCurrentSaturday = Table.AddColumn(InsertWeekEnding, "CurrentSaturday", each Date.AddDays([Date], -Date.DayOfWeek([Date],6)), type date),
    DateOffset = Table.AddColumn(InsertCurrentSaturday, "Offset", each Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01") - [CurrentSaturday]),
    #"Changed Type" = Table.TransformColumnTypes(DateOffset,{{"Offset", Int64.Type}}),
    InsertISOWeekFeb1 = Table.AddColumn(#"Changed Type", "ISOWeekFeb1", each if [Offset] > 6 then Date.FromText(Number.ToText(Date.Year([CurrentSaturday])-1) & "-02-01") else Date.FromText(Number.ToText(Date.Year([CurrentSaturday])) & "-02-01"),type date),
    InsertISOWeekYear = Table.AddColumn(InsertISOWeekFeb1, "ISOWeekYear", each Date.Year([ISOWeekFeb1])),
    InsertISOWeekFirstSat = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstSat", each if [CurrentSaturday] < [ISOWeekFeb1] 
        then Date.AddDays([CurrentSaturday],0) 
        else Date.AddDays([ISOWeekFeb1], - Date.DayOfWeek([ISOWeekFeb1],6) ), type date),
    InsertFYWeekNum = Table.AddColumn(InsertISOWeekFirstSat, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstSat]))+1) /7 )), type number),
    FiscalYear = Table.AddColumn(InsertFYWeekNum, "FY", each [ISOWeekYear]+1),
    InsertFYWeekID = Table.AddColumn(FiscalYear, "ISOWeekID", each [FY] * 100 + [ISOWeekNum], type number), 
    InsertIFYWeekNameLong = Table.AddColumn(InsertFYWeekID, "ISOWeekNameLong", each Text.From([FY]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Date.ToText([Date],"ddd")),
    #"Renamed Columns" = Table.RenameColumns(InsertIFYWeekNameLong,{{"ISOWeekNameLong", "FYWeekNameLong"}, {"ISOWeekID", "FYWeekID"}, {"ISOWeekNum", "FYWeekNum"}, {"ISOWeekFirstSat", "FYWeekFirstSat"}}),
    #"FY Quarter" = Table.AddColumn(#"Renamed Columns", "FY Quarter", each if [FYWeekNum] <= 13 then 1 else if [FYWeekNum] >= 14 and [FYWeekNum] <= 26 then 2 else if [FYWeekNum] >= 27 and [FYWeekNum] <= 39 then 3 else 4),
    #"Week of FY Quarter" = Table.AddColumn(#"FY Quarter", "Week of Quarter", each if [FYWeekNum] <> 53 then ([FYWeekNum] - (Number.RoundUp([FYWeekNum]/13)-1) * 13) else 14),
    #"Quarter Week ID" = Table.AddColumn(#"Week of FY Quarter", "QtrWeekID", each [FY Quarter]*100+[Week of Quarter]),
    #"FY Quarter ID" = Table.AddColumn(#"Quarter Week ID", "FYQtrID", each [FY]*100+[FY Quarter]),
    #"Changed Type1" = Table.TransformColumnTypes(#"FY Quarter ID",{{"FY Quarter", Int64.Type}, {"Week of Quarter", Int64.Type}, {"QtrWeekID", Int64.Type}, {"FYQtrID", Int64.Type}}),

    fnPeriod454a = (weekNum) => let 
      Periods = 
        { 
            {(x)=>x<5,  [P=1, M="Feb"]}, 
      {(x)=>x<10,  [P=2, M="Mar"]}, 
      {(x)=>x<14, [P=3, M="Apr"]}, 
      {(x)=>x<18, [P=4, M="May"]}, 
      {(x)=>x<23, [P=5, M="Jun"]}, 
      {(x)=>x<27, [P=6, M="Jul"]}, 
      {(x)=>x<31, [P=7, M="Aug"]}, 
      {(x)=>x<36, [P=8, M="Sep"]}, 
      {(x)=>x<40, [P=9, M="Oct"]}, 
            {(x)=>x<44, [P=10, M="Nov"]}, 
            {(x)=>x<49, [P=11, M="Dec"]}, 
            {(x)=>true, [P=12, M="Jan"]} 
        }, 
      Result = List.First(List.Select(Periods, each _{0}(weekNum))){1} 
    in 
      Result,

    InsertPeriod454 = Table.AddColumn(#"Changed Type1", "Period454Record", each fnPeriod454a([FYWeekNum])),
    #"Expanded Period454Record" = Table.ExpandRecordColumn(InsertPeriod454, "Period454Record", {"M", "P"}, {"M", "P"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Period454Record",{{"M", "FY Month Name"}, {"P", "FY Month ID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"CurrentSaturday", "Offset", "ISOWeekFeb1", "ISOWeekYear", "FYWeekFirstSat"})
in
    #"Removed Columns"

[Anonymous]

@dkay84_PowerBI & @Anonymous

Hello, 

Can any of you please tell me how to set the week Sunday to Saturday on your code?

Thank you so much 

[Anonymous]

Hi Thank you for the solution, but I have a question. The "end date & start date" in red colour - what is the date format? US or Normal? And how would you express the date eg: 20110331 or 2011mar31 or 201131mar?