Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
hey,
Proud to be a Super User!
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.
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.
hey,
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.