Create Custom Calender Table With info for custom dates

hey,

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.