Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Custom Fiscal Year Calendar

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Custom Fiscal Year Calendar

11-02-2016
10:40 AM

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.

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-06-2016
08:52 AM

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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2016
09:59 AM

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"

27 REPLIES 27

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2021
02:13 PM

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?

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

Any ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
07:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
08:01 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
08:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
08:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
08:30 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
03:19 PM

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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2016
12:55 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2016
01:18 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-06-2016
08:52 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2018
06:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2018
06:56 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018
02:43 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-01-2017
04:02 PM

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"]} },

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2016
09:59 AM

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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-27-2018
07:56 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2018
06:08 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018
02:46 PM

Hi fbaird, unfortunately it's been a while since a worked on this and I no longer have the report as I rolled off of that engagement and it stayed behind. From my memory it is either a date value (i.e. #date(year,month,day) or a date code like 20180301). I'm in the US so it was definitely the US version if any, but maybe this is can change if you system locale settings are not US?

EIther way, you only have a few choices to try out so see what works!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018
02:51 PM

Hi Dkay84,

Thank you for coming back to me on this. And yes you are right, I will give it whirl and see how we go

Thanks

Featured Topics

Top Solution Authors

User | Count |
---|---|

287 | |

148 | |

63 | |

57 | |

40 |

Top Kudoed Authors

User | Count |
---|---|

341 | |

216 | |

87 | |

67 | |

57 |