Forum Discussion
working day
Hi
i am struggling to count which date is which no. working day in a month?
suppose my date is 9/16/2019,so i want to know which number working day is it?
Aarti1 ,
First, I assume by "working day" you are referring to Weekdays only and want to exclude Saturday and Sunday.
On this basis, I used my Dim_Date (ie Calendar) table and came up with the following:
WorkingDayofMonth = RANKX( FILTER( DIM_Date, DIM_Date[MMYYYY]=EARLIER(DIM_Date[MMYYYY]) && DIM_Date[IsWeekday] = TRUE() ), DIM_Date[DayOfMonth],,ASC,Skip )Date DayName IsWeekday DayOfMonth WorkingDayofMonth 1-Sep-19 Sunday False 1 1 2-Sep-19 Monday True 2 1 3-Sep-19 Tuesday True 3 2 4-Sep-19 Wednesday True 4 3 5-Sep-19 Thursday True 5 4 6-Sep-19 Friday True 6 5 7-Sep-19 Saturday False 7 6 8-Sep-19 Sunday False 8 6 9-Sep-19 Monday True 9 6 10-Sep-19 Tuesday True 10 7 11-Sep-19 Wednesday True 11 8 12-Sep-19 Thursday True 12 9 13-Sep-19 Friday True 13 10 14-Sep-19 Saturday False 14 11 15-Sep-19 Sunday False 15 11 16-Sep-19 Monday True 16 11 17-Sep-19 Tuesday True 17 12 18-Sep-19 Wednesday True 18 13 19-Sep-19 Thursday True 19 14 20-Sep-19 Friday True 20 15 21-Sep-19 Saturday False 21 16 22-Sep-19 Sunday False 22 16 23-Sep-19 Monday True 23 16 24-Sep-19 Tuesday True 24 17 25-Sep-19 Wednesday True 25 18 26-Sep-19 Thursday True 26 19 27-Sep-19 Friday True 27 20 28-Sep-19 Saturday False 28 21 29-Sep-19 Sunday False 29 21 30-Sep-19 Monday True 30 21 So, 9/16/2019 is the 11th Working Day of that Month Year.
Hope this is a solution that can work for you.
Regards,
1 Reply
- rsbinCommunity Champion
Aarti1 ,
First, I assume by "working day" you are referring to Weekdays only and want to exclude Saturday and Sunday.
On this basis, I used my Dim_Date (ie Calendar) table and came up with the following:
WorkingDayofMonth = RANKX( FILTER( DIM_Date, DIM_Date[MMYYYY]=EARLIER(DIM_Date[MMYYYY]) && DIM_Date[IsWeekday] = TRUE() ), DIM_Date[DayOfMonth],,ASC,Skip )Date DayName IsWeekday DayOfMonth WorkingDayofMonth 1-Sep-19 Sunday False 1 1 2-Sep-19 Monday True 2 1 3-Sep-19 Tuesday True 3 2 4-Sep-19 Wednesday True 4 3 5-Sep-19 Thursday True 5 4 6-Sep-19 Friday True 6 5 7-Sep-19 Saturday False 7 6 8-Sep-19 Sunday False 8 6 9-Sep-19 Monday True 9 6 10-Sep-19 Tuesday True 10 7 11-Sep-19 Wednesday True 11 8 12-Sep-19 Thursday True 12 9 13-Sep-19 Friday True 13 10 14-Sep-19 Saturday False 14 11 15-Sep-19 Sunday False 15 11 16-Sep-19 Monday True 16 11 17-Sep-19 Tuesday True 17 12 18-Sep-19 Wednesday True 18 13 19-Sep-19 Thursday True 19 14 20-Sep-19 Friday True 20 15 21-Sep-19 Saturday False 21 16 22-Sep-19 Sunday False 22 16 23-Sep-19 Monday True 23 16 24-Sep-19 Tuesday True 24 17 25-Sep-19 Wednesday True 25 18 26-Sep-19 Thursday True 26 19 27-Sep-19 Friday True 27 20 28-Sep-19 Saturday False 28 21 29-Sep-19 Sunday False 29 21 30-Sep-19 Monday True 30 21 So, 9/16/2019 is the 11th Working Day of that Month Year.
Hope this is a solution that can work for you.
Regards,