cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Custom start of the year and month as 1st Tueday

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?

1 ACCEPTED SOLUTION
Super User

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)),
"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 ...

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.

Super User

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)),
"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 ...

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.