Adding Fiscal Months and 4-5-4 format to Dates Table

Power Query
1

Editing the Dates Table Code to include Fiscal Months and customizing the fiscal month start date

I’m very new to DAX and creating M code, but I’m excited that I found this forum! I’ve been trying to get the Dates Table code to add fiscal months and have them start on the first Monday of every month. Our fiscal year starts in January and is a 4-5-4 format, so as an example, this year’s fiscal year started on 1/2/23 and the end of the first fiscal month was on 2/5/23. I would like to run the table from 1/1/2020 to 12/31/25. Can the Dates Table code be modified to accomplish this?

image

2

Hi @nasir58,

Still have a couple of questions…
Is the weekly pattern you’ve shared (shown below) applicable to all years?

Quarter Weekly pattern
Q1 5-4-4
Q2 4-5-4
Q3 5-4-4
Q4 5-4-4

Calendars based on 13 week quarters/ 52 weeks have a year with 364 days.
Does that mean your FY startdate in 2020 was Jan 6th? Jan 2nd 2023 - (3 x 364)

1 Like
3

Hey @Melissa!

Yes, that pattern is for all years.

4

Hi @nasir58,

Give this custom function a go.

let fnDateTable = ( FirstFYDate as date, optional StartOfWeekDay as number, optional AddYears as number, optional Holidays as list ) as table =>
    let
        FYStartDate = FirstFYDate, // #date(2023, 1, 2), // Enter your first FY StartDate here
        StartOfWeekNum = if List.Contains({0..6}, StartOfWeekDay) then StartOfWeekDay else 0, // Day.Sunday =0, Day.Monday =1 etc
        CurrentDate = Date.From( DateTimeZone.FixedUtcNow()),
        ExtendYears = if AddYears = null or AddYears =0 then Date.Year( CurrentDate ) -  Date.Year( FYStartDate ) +1 else AddYears,
        StartDate = Number.From( FYStartDate ),
        EndDate = StartDate + ( 364 * ExtendYears ) -1,
        ListDates = {StartDate..EndDate},
        ConvertedToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "DateKey"}}),
        DateKey = Table.TransformColumnTypes(RenamedColumns,{{"DateKey", type date}}),
        DayID = Table.AddIndexColumn(DateKey, "DayID", 1, 1, Int64.Type),
        YearID = Table.AddColumn(DayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1, Int64.Type),
        MonthID = Table.AddColumn(YearID, "MonthID", each let n = Number.Mod([DayID]-1,364) in ([YearID]-1) * 12 +
            ( if n < 35 then 1 else if n < 63 then 2 else if n < 91 then 3 else if n < 119 then 4 else if n < 154 then 5 else if n < 182 then 6 else 
            if n < 217 then 7 else if n < 245 then 8 else if n < 273 then 9 else if n < 308 then 10 else if n < 336 then 11 else 12), Int64.Type),
        WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
        InsertYear = Table.AddColumn(WeekID, "Year", each Date.Year([DateKey]), Int64.Type),
        InsertMonth = Table.AddColumn(InsertYear, "cMonth", each Date.Month([DateKey]), Int64.Type),
        InsertDayOfMonth = Table.AddColumn(InsertMonth, "Day", each Date.Day([DateKey]), Int64.Type),
        InsertMonthName = Table.AddColumn(InsertDayOfMonth, "cMonth Name", each Date.MonthName([DateKey]), type text),
        InsertEndOfMonth = Table.AddColumn(InsertMonthName, "End of cMonth", each Date.EndOfMonth([DateKey]), type date),
        InsertDayName = Table.AddColumn(InsertEndOfMonth, "Day Name", each Date.DayOfWeekName([DateKey]), type text),
        StartOfWeek = Table.AddColumn(InsertDayName, "Start of Week", each Date.StartOfWeek([DateKey], StartOfWeekNum), type date),
        EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], StartOfWeekNum), type date),
        InsertYearFY = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) -1 + [YearID], Int64.Type ),
        InsertPeriodFY = Table.AddColumn(InsertYearFY, "FY Period", each [MonthID] - (( [YearID] -1 ) * 12 ), Int64.Type ),
        InsertWeekFY = Table.AddColumn(InsertPeriodFY, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
        InsertDayOfFY = Table.AddColumn(InsertWeekFY, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type),
        InsertFYPeriod = Table.AddColumn(InsertDayOfFY, "FY & Period", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Period]), 2, "0"), type text),
        InsertFYWeek = Table.AddColumn(InsertFYPeriod, "FY & Week", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Week]), 2, "0"), type text),
        InsertIsAfterToday = Table.AddColumn(InsertFYWeek, "IsAfterToday", each not ([DateKey] <= Date.From(CurrentDate)), type logical),
        InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([DateKey], Day.Monday) > 4 then false else true, type logical),
        InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [DateKey] ), if Holidays = null then type text else type logical)
    in
        InsertIsHoliday
in
fnDateTable

.
It takes these parameters:

image

  1. FirstFYDate = enter the First Fiscal Year Start Date for your calendar.
  2. StartOfWeekDay = optionally enter a 0 = Sun up to 6 = Sat
  3. AddYears = optionally enter the number of years to include in the calendar.
  4. Holidays = optionally provide a list with holiday dates

To emphasise:
cMonth, cMonth Name and End of Month, relate to Calendar Months
while MonthID, FY Period and FY& Period, relate to Fiscal Periods

Attached a sample PBIX
Custom Dates table.pbix (70.6 KB)

I hope this is helpful

3 Likes