cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bml123
Post Patron
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
sevenhills
Super User
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)),
    #"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"

 

 

sevenhills_0-1640305682336.png

 

quick verify:

sevenhills_1-1640305706677.png

 

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.

 

 

 

 

View solution in original post

1 REPLY 1
sevenhills
Super User
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)),
    #"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"

 

 

sevenhills_0-1640305682336.png

 

quick verify:

sevenhills_1-1640305706677.png

 

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.

 

 

 

 

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.