Forum Discussion

Aarti1's avatar
Aarti1
New Member
3 years ago
Solved

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?
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    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,

rsbin's avatar
rsbin
Community Champion
3 years ago

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,