- 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
- Report Inappropriate Content
Custom Fiscal Year Calendar
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
Hi,
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
@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
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
Can I get this PIBX?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much! this is exactly what I'm looking for!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
@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
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
03-13-2024 07:14 AM | |||
06-10-2024 11:18 AM | |||
09-05-2024 07:19 AM | |||
08-19-2024 01:22 PM | |||
10-12-2023 03:09 PM |
User | Count |
---|---|
104 | |
75 | |
43 | |
39 | |
32 |
User | Count |
---|---|
168 | |
90 | |
65 | |
46 | |
44 |