Create Custom Calender Table With info for custom dates

[Apssawhney]

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. 

[StefanoGrimaldi · Accepted solution]

hey,

Column =

var weeknum1 = (1 + WEEKNUM ( 'Table'[Date] )-WEEKNUM( STARTOFMONTH ('Table'[Date]))) //finds the weeknumber

var daynumber = WEEKDAY('Table'[Date],2) //tuesdays in this format its number 2

var 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 out

with 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 weeknum 

if this heleped give some kudos, and if solved your question mark as answer for others to find. 

[Icey · Accepted solution]

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.

[StefanoGrimaldi · Accepted solution]

hey,

Column =

var weeknum1 = (1 + WEEKNUM ( 'Table'[Date] )-WEEKNUM( STARTOFMONTH ('Table'[Date]))) //finds the weeknumber

var daynumber = WEEKDAY('Table'[Date],2) //tuesdays in this format its number 2

var 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 out

with 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 weeknum 

if this heleped give some kudos, and if solved your question mark as answer for others to find.