Forum Discussion
Fiscal Calendar Date Table
Hi All!
I am looking for the most efficient way to create a Fiscal Calendar Date Table (Fiscal Year starting in July). I'm looking for the calendar to not have a specific beginning date (min) or ending date (max) date, but is coinciding with the dates that are uploaded in my fact table.
Thank you for your help!
Christina
Please try out this approach that dynamically gets the start and end dates dynamically from your fact table. The first is the M/Power Query version (note this may cause performance problems as it will effectively load your fact table a second time to get these dates). The 2nd is the DAX version of same.
let FactDates = List.Buffer(List.Distinct(FactQuery[DateColumn])), StartDate = List.Min(FactDates), EndDate = List.Max (FactDates), DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)), InitialTable = Table.FromColumns({DateList}, {"Date"}), #"Changed Type" = Table.TransformColumnTypes(InitialTable,{{"Date", type date}}), AddFY = Table.AddColumn(#"Changed Type", "FY", each Date.Year(Date.AddMonths([Date], 6))), AddFQ = Table.AddColumn(AddFY, "FQ", each Date.QuarterOfYear(Date.AddMonths([Date], 6))), AddFM = Table.AddColumn(AddFQ, "FM", each Date.Month(Date.AddMonths([Date], 6))), #"Added Custom" = Table.AddColumn(AddFM, "FW", each let FYstart = #date([FY]-1, 7, 1), result = Number.RoundUp((Duration.TotalDays([Date] - FYstart)+1)/7, 0) in result), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FY", Int64.Type}, {"FQ", Int64.Type}, {"FM", Int64.Type}, {"FW", Int64.Type}}) in #"Changed Type1"FiscalDates = ADDCOLUMNS ( CALENDAR ( MIN ( Sales[SaleDate] ), MAX ( Sales[SaleDate] ) ), "FY", YEAR ( EOMONTH ( [Date], 6 ) ), "FQ", QUARTER ( EOMONTH ( [Date], 6 ) ), "FM", MONTH ( EOMONTH ( [Date], 6 ) ), "FW", ROUNDUP ( ( INT ( [Date] - DATE ( YEAR ( EOMONTH ( [Date], 6 ) ) - 1, 7, 1 ) ) + 1 ) / 7, 0 ) )Pat
5 Replies
Anonymous Well, that's a good question. I know that Enterprise DNA has a really good date table that Melissa de Korte put together: Extended Date Table Power Query M Function - Enterprise DNA. Using The M Function To Create An Extended Power BI Date Table Code | Enterprise DNA.
You can also do it in DAX: DAX Custom 445 Calendar - Microsoft Power BI Community
I suppose the DAX approach would have the advantage of being able to specify MAX and MIN of the dates in your fact table although you might be able to do something similar in Power Query with a little work. Essentially, create queries for grabbing max and then min and use them as parameters in your Power Query M function more or less.
Greg_Deckler - Thank you for the response, but I'm NOT looking to include a specific start and end date. I did try to using the M function to create a Fiscal Year Date Calendar and although it's on the right track of what I'm looking for, It didn't provide me fiscal month numbers, fiscal weeks (just the week number), and if my FY starts in July and Ends in June, I am looking for the year to be formatted as: FY2223 (for example) not just the year it begins in.
Please try out this approach that dynamically gets the start and end dates dynamically from your fact table. The first is the M/Power Query version (note this may cause performance problems as it will effectively load your fact table a second time to get these dates). The 2nd is the DAX version of same.
let FactDates = List.Buffer(List.Distinct(FactQuery[DateColumn])), StartDate = List.Min(FactDates), EndDate = List.Max (FactDates), DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)), InitialTable = Table.FromColumns({DateList}, {"Date"}), #"Changed Type" = Table.TransformColumnTypes(InitialTable,{{"Date", type date}}), AddFY = Table.AddColumn(#"Changed Type", "FY", each Date.Year(Date.AddMonths([Date], 6))), AddFQ = Table.AddColumn(AddFY, "FQ", each Date.QuarterOfYear(Date.AddMonths([Date], 6))), AddFM = Table.AddColumn(AddFQ, "FM", each Date.Month(Date.AddMonths([Date], 6))), #"Added Custom" = Table.AddColumn(AddFM, "FW", each let FYstart = #date([FY]-1, 7, 1), result = Number.RoundUp((Duration.TotalDays([Date] - FYstart)+1)/7, 0) in result), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FY", Int64.Type}, {"FQ", Int64.Type}, {"FM", Int64.Type}, {"FW", Int64.Type}}) in #"Changed Type1"FiscalDates = ADDCOLUMNS ( CALENDAR ( MIN ( Sales[SaleDate] ), MAX ( Sales[SaleDate] ) ), "FY", YEAR ( EOMONTH ( [Date], 6 ) ), "FQ", QUARTER ( EOMONTH ( [Date], 6 ) ), "FM", MONTH ( EOMONTH ( [Date], 6 ) ), "FW", ROUNDUP ( ( INT ( [Date] - DATE ( YEAR ( EOMONTH ( [Date], 6 ) ) - 1, 7, 1 ) ) + 1 ) / 7, 0 ) )Pat
ppm1 - It worked! Thank you!! 🙂
Hello! Could the DAX solution be used for a fiscal calendar of 01/01 - 12/31 that uses a 28 day month? December containing the extra weeks.
