topic Re: Need help in date table creation in DAX Commands and Tips

Need help in date table creation

Navaneetharaju_ — Thu, 04 Apr 2024 05:56:58 GMT

Hi All,

I have creating the date table for my business purpose -

Date table =

Date = CALENDAR(DATE(2014,01,01), DATE(YEAR(TODAY()),12, 31))

I have created the end date year as a year of today and 12 the month and 31st day of the month.

And also,

i want to find the date column to find the last day of the month,

In date table we have to create the column that indicate all the last day in my table,

And 1st monday of the every month the same way i want to create the indicator or flag in my date table,

1st monday of the month indicator,
2nd monday of the month indicator,

3rd monday of the month indicator,

4th monday of the month indicator

5th monday of the month indicator

the same way, i have to create a indicators or flag for all 2nd mon, tue, wed, thur, fri, sat, sun till the last mon, last tue, last wed, last thur, last fri, last sat, last sun.

Kindly provide me the calculated column to build this.

Re: Need help in date table creation

Joe_Barry — Thu, 04 Apr 2024 07:06:50 GMT

Hi Navaneetharaju_

Here is a MQuery I use to create a date table. It shows the last 5 years and next 6 years automatically, you can adapt this for your needs. It also has the last day of the month amongst other columns

let // configurations start Today = Date.From(DateTime.LocalNow()), // today's date FromYear = Date.Year(DateTime.LocalNow()) -5, // set the start year of the date dimension. dates start from 1st of January of this year ToYear = Date.Year(DateTime.LocalNow()) +6, // set the end year of the date dimension. dates end at 31st of December of this year StartofFiscalYear = 7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7 firstDayofWeek = Day.Monday, // set the week's start day, values: Day.Monday, Day, Sunday.... // configuration end FromDate = #date(FromYear,1,1), ToDate = #date(ToYear,12,31), Source = List.Dates( FromDate, Duration.Days(ToDate-FromDate)+1, #duration(1,0,0,0) ), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date), #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date), #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type), #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date), #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date), #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type), #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type), #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type), #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text), #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type), #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date), #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date), #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date], firstDayofWeek), Int64.Type), #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date], firstDayofWeek), Int64.Type), #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date], firstDayofWeek), type date), #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date], firstDayofWeek), type date), FiscalMonthBaseIndex = 13-StartofFiscalYear, adjustedFiscalMonthBaseIndex = if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex, #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date], adjustedFiscalMonthBaseIndex)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"FiscalBaseDate", type date}}), #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1", {{"Year.1", "Fiscal Year"}}), #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type), #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1", {{"Quarter.1", "Fiscal Quarter"}}), #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type), #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1", {{"Month.1", "Fiscal Month"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3", {"FiscalBaseDate"}), #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date] - Today, type duration), #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}), #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days", {{"Age", "Day Offset"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12) +([Month]-Date.Month(Today))), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1", {{"Month Offset", Int64.Type}}), #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2", {{"Year Offset", Int64.Type}}), #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4) +([Quarter]-Date.QuarterOfYear(Today))), #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3", {{"Quarter Offset", Int64.Type}}), #"Added custom" = Table.AddColumn(#"Changed Type4", "Datekey", each Date.ToText(Date.From([Date]), "yyyyMMdd")), #"Changed column type" = Table.TransformColumnTypes(#"Added custom", {{"Datekey", Int64.Type}}), #"Added custom 1" = Table.AddColumn(#"Changed column type", "Month-Text", each Text.PadStart( Text.From([Month]), 2, "0")), #"Changed column type 1" = Table.TransformColumnTypes(#"Added custom 1", {{"Month-Text", type text}}), #"Inserted merged column" = Table.AddColumn(#"Changed column type 1", "Month-Year", each Text.Combine({[#"Month-Text"], Text.From([Year])}, "-"), type text), #"Extracted first characters" = Table.TransformColumns(#"Inserted merged column", {{"Month Name", each Text.Start(_, 3), type text}}), #"Extracted first characters 1" = Table.TransformColumns(#"Extracted first characters", {{"Day Name", each Text.Start(_, 3), type text}}) in #"Extracted first characters 1"

I then added this in DAX , seems to work.

1st Monday of Month = IF ('DIM Date Test'[Day Name] = "Mon" && DAY('DIM Date Test'[Date]) < 7 , 1, 0)

Hope it helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution

Re: Need help in date table creation

johnbasha33 — Fri, 05 Apr 2024 09:30:39 GMT

Navaneetharaju_
Date table =
ADDCOLUMNS(
CALENDAR(DATE(2014, 01, 01), DATE(YEAR(TODAY()), 12, 31)),
"LastDayOfMonth", EOMONTH([Date], 0),
"FirstMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) <= 7, 1, 0),
"SecondMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthMondayOfMonth", IF(WEEKDAY([Date]) = 2 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastMondayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 2, 1, 0),
"FirstTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) <= 7, 1, 0),
"SecondTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthTuesdayOfMonth", IF(WEEKDAY([Date]) = 3 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastTuesdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 3, 1, 0),
"FirstWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) <= 7, 1, 0),
"SecondWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthWednesdayOfMonth", IF(WEEKDAY([Date]) = 4 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastWednesdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 4, 1, 0),
"FirstThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) <= 7, 1, 0),
"SecondThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthThursdayOfMonth", IF(WEEKDAY([Date]) = 5 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastThursdayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 5, 1, 0),
"FirstFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) <= 7, 1, 0),
"SecondFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),
"FifthFridayOfMonth", IF(WEEKDAY([Date]) = 6 && DAY([Date]) > 28 && DAY([Date]) <= 31, 1, 0),
"LastFridayOfMonth", IF(WEEKDAY(EOMONTH([Date], 0)) = 6, 1, 0),
"FirstSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) <= 7, 1, 0),
"SecondSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 7 && DAY([Date]) <= 14, 1, 0),
"ThirdSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 14 && DAY([Date]) <= 21, 1, 0),
"FourthSaturdayOfMonth", IF(WEEKDAY([Date]) = 7 && DAY([Date]) > 21 && DAY([Date]) <= 28, 1, 0),

Re: Need help in date table creation

Anonymous — Mon, 08 Apr 2024 01:42:07 GMT

Hi Navaneetharaju_ ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Need help in date table creation

Navaneetharaju_ — Wed, 10 Apr 2024 09:59:24 GMT

johnbasha33 , Joe_Barry ,
Thanks for your response,

Responses that provided from you both works fine.

Let keep in touch.

Re: Need help in date table creation

johnbasha33 — Wed, 10 Apr 2024 10:09:23 GMT

Navaneetharaju_ glad it helped you.

can you please give Kudos and mark it as solution..

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!