cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Apssawhney
New Member

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 - 

 

DateActivity
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/2021Microsoft Patch Released
12/01/2021 
13/01/2021Non-prod servers patching
14/01/2021Work 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/2021Prod servers patching/Work stations production patching
22/01/2021Prod servers patching/Work stations production patching
23/01/2021Prod 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. 

 

 

2 ACCEPTED SOLUTIONS
StefanoGrimaldi
Super User
Super User

hey,

 

StefanoGrimaldi_0-1616687288772.png

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. 




Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

Icey
Community Support
Community Support

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"
    )

 

activity - DAX.JPG

 

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"

 

activity - M.JPG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

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"
    )

 

activity - DAX.JPG

 

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"

 

activity - M.JPG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

StefanoGrimaldi
Super User
Super User

hey,

 

StefanoGrimaldi_0-1616687288772.png

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. 




Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors