Forum Discussion
Solved
Create Custom Calender Table With info for custom dates
Hi Team,
Can you help in creating a calendar table which has all the dates but few dates per month are marked based on the below conditions -
1. Microsoft Patch released (2nd Tuesday of every month)
2. Non-prod servers patching Starts on Thursday (after the 2nd Tuesday of the month) at 8 PM PST.
3. Work stations patching for UAT Machines starts on Friday (after the 2nd Tuesday of the month) at 1 PM CT.
4. Prod servers patching starts on Friday (after the 3rd Tuesday of the month) at 8 PM PST.
5. Work stations production patching starts on Friday (after the 3rd Tuesday of the month) at 6 PM CT
6. Freeze Period (25th Dec-30th Dec)
The table would look someting like this -
| Date | Activity |
| 01/01/2021 | |
| 02/01/2021 | |
| 03/01/2021 | |
| 04/01/2021 | |
| 05/01/2021 | |
| 06/01/2021 | |
| 07/01/2021 | |
| 08/01/2021 | |
| 09/01/2021 | |
| 10/01/2021 | |
| 11/01/2021 | Microsoft Patch Released |
| 12/01/2021 | |
| 13/01/2021 | Non-prod servers patching |
| 14/01/2021 | Work stations patching for UAT Machines |
| 15/01/2021 | |
| 16/01/2021 | |
| 17/01/2021 | |
| 18/01/2021 | |
| 19/01/2021 | |
| 20/01/2021 | |
| 21/01/2021 | Prod servers patching/Work stations production patching |
| 22/01/2021 | Prod servers patching/Work stations production patching |
| 23/01/2021 | Prod servers patching/Work stations production patching |
| 24/01/2021 | |
| 25/01/2021 | |
| 26/01/2021 | |
| 27/01/2021 | |
| 28/01/2021 | |
| 29/01/2021 |
Can anyone assist here.
hey,
Column =var weeknum1 = (1 + WEEKNUM ( 'Table'[Date] )-WEEKNUM( STARTOFMONTH ('Table'[Date]))) //finds the weeknumbervar daynumber = WEEKDAY('Table'[Date],2) //tuesdays in this format its number 2var out = IF( AND(weeknum1=2,daynumber=2),"Microsoft Patch released",IF(AND(weeknum1=2,daynumber=4),"Non-prod servers patching",IF(AND(weeknum1=2,daynumber=5),"Work stations patching for UAT Machines","")))return outwith this logic you can add the other parameters to give the wanted output but basically the 2 variable get the exact number of the week and weekday and month weeknumif this heleped give some kudos, and if solved your question mark as answer for others to find.Hi Apssawhney ,
How do you get this?
Date Activity 21/01/2021 Prod servers patching/Work stations production patching 22/01/2021 Prod servers patching/Work stations production patching 23/01/2021 Prod servers patching/Work stations production patching When I create column based on your logic, the result is like below:
DAX:
Activity = VAR YearWeek_ = WEEKNUM ( [Date], 2 ) VAR StartofMonthWeek_ = WEEKNUM ( STARTOFMONTH ( 'Calendar (DAX)'[Date] ), 2 ) VAR MonthWeek_ = YearWeek_ - StartofMonthWeek_ + 1 VAR WeekDay_ = WEEKDAY ( [Date], 2 ) VAR Month_ = MONTH ( [Date] ) VAR Day_ = DAY ( [Date] ) RETURN SWITCH ( TRUE (), MonthWeek_ = 2 && WeekDay_ = 2, "Microsoft Patch released", MonthWeek_ = 2 && WeekDay_ = 4, "Non-prod servers patching", MonthWeek_ = 2 && WeekDay_ = 5, "Work stations patching for UAT Machines", MonthWeek_ = 3 && WeekDay_ = 5, "Prod servers patching / Work stations production patching", Month_ = 12 && Day_ >= 25 && Day_ <= 30, "Freeze Period" )M:
let StartDate = #date(2021,1,1), EndDate = #date(2021,12,31), DayCount = Duration.Days(Duration.From( EndDate - StartDate )), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(RenamedColumns, "Activity", each let MonthWeek_ = Date.WeekOfMonth ([Date],1), WeekDay_ = Date.DayOfWeek ( [Date], 2 ), Month_ = Date.Month([Date]), Day_ = Date.DayOfWeek([Date],1) in if MonthWeek_ = 2 then if WeekDay_ = 2 then "Microsoft Patch released" else if WeekDay_ = 4 then "Non-prod servers patching" else if WeekDay_ = 5 then "Work stations patching for UAT Machines" else null else if MonthWeek_ = 3 and WeekDay_ = 5 then "Prod servers patching / Work stations production patching" else if Month_ = 12 and Day_ >= 25 and Day_ <= 30 then "Freeze Period" else null) in #"Added Custom"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
hey,
Column =var weeknum1 = (1 + WEEKNUM ( 'Table'[Date] )-WEEKNUM( STARTOFMONTH ('Table'[Date]))) //finds the weeknumbervar daynumber = WEEKDAY('Table'[Date],2) //tuesdays in this format its number 2var out = IF( AND(weeknum1=2,daynumber=2),"Microsoft Patch released",IF(AND(weeknum1=2,daynumber=4),"Non-prod servers patching",IF(AND(weeknum1=2,daynumber=5),"Work stations patching for UAT Machines","")))return outwith this logic you can add the other parameters to give the wanted output but basically the 2 variable get the exact number of the week and weekday and month weeknumif this heleped give some kudos, and if solved your question mark as answer for others to find.Hi Apssawhney ,
How do you get this?
Date Activity 21/01/2021 Prod servers patching/Work stations production patching 22/01/2021 Prod servers patching/Work stations production patching 23/01/2021 Prod servers patching/Work stations production patching When I create column based on your logic, the result is like below:
DAX:
Activity = VAR YearWeek_ = WEEKNUM ( [Date], 2 ) VAR StartofMonthWeek_ = WEEKNUM ( STARTOFMONTH ( 'Calendar (DAX)'[Date] ), 2 ) VAR MonthWeek_ = YearWeek_ - StartofMonthWeek_ + 1 VAR WeekDay_ = WEEKDAY ( [Date], 2 ) VAR Month_ = MONTH ( [Date] ) VAR Day_ = DAY ( [Date] ) RETURN SWITCH ( TRUE (), MonthWeek_ = 2 && WeekDay_ = 2, "Microsoft Patch released", MonthWeek_ = 2 && WeekDay_ = 4, "Non-prod servers patching", MonthWeek_ = 2 && WeekDay_ = 5, "Work stations patching for UAT Machines", MonthWeek_ = 3 && WeekDay_ = 5, "Prod servers patching / Work stations production patching", Month_ = 12 && Day_ >= 25 && Day_ <= 30, "Freeze Period" )M:
let StartDate = #date(2021,1,1), EndDate = #date(2021,12,31), DayCount = Duration.Days(Duration.From( EndDate - StartDate )), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(RenamedColumns, "Activity", each let MonthWeek_ = Date.WeekOfMonth ([Date],1), WeekDay_ = Date.DayOfWeek ( [Date], 2 ), Month_ = Date.Month([Date]), Day_ = Date.DayOfWeek([Date],1) in if MonthWeek_ = 2 then if WeekDay_ = 2 then "Microsoft Patch released" else if WeekDay_ = 4 then "Non-prod servers patching" else if WeekDay_ = 5 then "Work stations patching for UAT Machines" else null else if MonthWeek_ = 3 and WeekDay_ = 5 then "Prod servers patching / Work stations production patching" else if Month_ = 12 and Day_ >= 25 and Day_ <= 30 then "Freeze Period" else null) in #"Added Custom"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.