Custom start of the year and month as 1st Tueday

[bml123]

HI,

I have a standard calendar table but the start of the year should be 1st Tuesday in a month. For e.g, for 2021, the start of the year is 5th January and 1,2,3,4 in January 2021 are treated as the last week of December 2020.  How do I customise the start of the year and end of the year based on this rule? Also need to customise the month name and number - for Dec 2021, until 3rd of January it should be considered as December. For September 2021, October 1, 2, 3, 4 should fall under September 5th week and October month should start from 5th October.

So, in any month first Tuesday should be my start of the month. date before the 1st Tuesday in a month which is Monday should be the last day of the previous month.

How do I achieve this?

[sevenhills · Accepted solution]

Step 1:

It will be easy if you think as

* Calendar dates as calendar dates

* your custom dates a fiscal dates

* build the table with both calendar and fiscal dates ~ static data. I recommend to build using "M", you can do the same in DAX too

Step 2: Build table

let
    StartDate = #date(2021, 1, 1),
    EndDate = #date(2021,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Change Year to Text" = Table.TransformColumnTypes(#"Inserted Year",{{"Year", type text}}),
    #"Inserted Day" = Table.AddColumn(#"Change Year to Text", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name Short" = Table.AddColumn(#"Inserted Day of Year", "Day Name Short", each Text.Start(Date.DayOfWeekName([FullDateAlternateKey]),3)),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name Short", "Fiscal - Begin of Month", each Date.StartOfMonth([FullDateAlternateKey])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal - First Tuesday of Month", each Date.EndOfWeek([#"Fiscal - Begin of Month"], Day.Wednesday)),
    #"Inserted Day Name" = Table.AddColumn(#"Added Custom1", 
"Fiscal - First Tuesday - Day(verify)"
, each Date.DayOfWeekName([#"Fiscal - First Tuesday of Month"]), type text)
in
    #"Inserted Day Name"

quick verify:

Note:

• you may need to expand this Fiscal columns per your needs. like adding fiscal year, fiscal month ... 
• Rename the Fiscal columns to your needs, I added long names for your explanation

Step 3:

Based on it, you can have the data in all tables as calendar dates. 

Step 4:

For all reporting purpose, you use your Fiscal date columns as your custom date columns.