Company Financial Period, DAX or M Code?

Power Query
9

Hi @DarrenG,

Wanted to share this M code for a 13 Period Date table with you.

let
    FYStartDate = #date(2019, 9, 29), // Enter your FY StartDate here
    StartDate = Number.From( FYStartDate ),
    Years = Date.Year( DateTimeZone.FixedUtcNow() ) -  Date.Year( FYStartDate ) +1,
    EndDate = StartDate + ( 364 * Years ),
    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),
    //QuarterID = Table.AddColumn(YearID, "QuarterID", each Number.RoundDown(([DayID]-1)/91)+1, Int64.Type),
    MonthID = Table.AddColumn(YearID, "MonthID", each Number.RoundDown(([DayID]-1)/28)+1, Int64.Type),
    WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
    StartOfWeek = Table.AddColumn(WeekID, "Start of Week", each Date.StartOfWeek([DateKey], Day.Sunday), type date),
    EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], Day.Sunday), type date),
    InsertYear = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) -1 + [YearID], Int64.Type ),
    InsertPeriod = Table.AddColumn(InsertYear, "FY Period", each [MonthID] - (( [YearID] -1 ) * 13 ), Int64.Type ),
    InsertWeek = Table.AddColumn(InsertPeriod, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
    InsertDay = Table.AddColumn(InsertWeek, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type)
in
    InsertDay

.
It’s based on this article.

I hope you find it useful.

1 Like