Fiscal Calendar Date Table

[Anonymous]

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

[ppm1 · Accepted solution]

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

[akmillerlease]

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.

[Anonymous]

@ppm1  - It worked!  Thank you!! 🙂

[Greg_Deckler]

@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.

[Anonymous]

@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.