topic Re: Fiscal Calendar Date Table in DAX Commands and Tips

Fiscal Calendar Date Table

Anonymous — Fri, 27 Jan 2023 21:10:36 GMT

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

Re: Fiscal Calendar Date Table

Greg_Deckler — Fri, 27 Jan 2023 21:34:30 GMT

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.

Re: Fiscal Calendar Date Table

Anonymous — Fri, 27 Jan 2023 22:37:29 GMT

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.

Re: Fiscal Calendar Date Table

ppm1 — Sat, 28 Jan 2023 00:22:36 GMT

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

Re: Fiscal Calendar Date Table

Anonymous — Tue, 31 Jan 2023 14:22:01 GMT

ppm1 - It worked! Thank you!! 🙂

Re: Fiscal Calendar Date Table

akmillerlease — Thu, 07 Sep 2023 15:18:34 GMT

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.