Calculate working days for the month when Fri, Sat & Sun should be same number.

[liberty20]

Hi all,

Trying to achieve the following output for each month in Power BI.

Date	Day
01 December 2021	1
02 December 2021	2
03 December 2021	3
04 December 2021	3
05 December 2021	3
06 December 2021	4
07 December 2021	5
08 December 2021	6
09 December 2021	7
10 December 2021	8
11 December 2021	8
12 December 2021	8
13 December 2021	9
14 December 2021	10
15 December 2021	11
16 December 2021	12
17 December 2021	13
18 December 2021	13
19 December 2021	13
20 December 2021	14
21 December 2021	15
22 December 2021	16
23 December 2021	17
24 December 2021	18
25 December 2021	18
26 December 2021	18
27 December 2021	19
28 December 2021	20
29 December 2021	21
30 December 2021	22
31 December 2021	23
01 January 2022	23
02 January 2022	23
03 January 2022	1
04 January 2022	2
05 January 2022	3
06 January 2022	4

Kindly help with the above.

Thank You!

[Fowmy · Accepted solution]

@liberty20 

I created a calculated column to get the desired results, please check and let me know if it works for you. I have attached a PBIX file below.

VAR __DATE1 =    
    COUNTROWS(
        FILTER(           
            DATESMTD('Dates'[Date]),
            WEEKDAY( 'Dates'[Date] , 2) in {1,2,3,4,5}            
        )
    )
VAR __DATE2 =    
    COUNTROWS(
        FILTER(
            PARALLELPERIOD('Dates'[Date], -1 ,MONTH),
            WEEKDAY( 'Dates'[Date] , 2) in {1,2,3,4,5} 
        )
    )
RETURN
COALESCE( __DATE1, __DATE2)

[Fowmy]

@liberty20 

Can you provide more details on the following 

1. What's the logic when a new month starts? 
2. What if the 1st date in the table starts on Saturday? Should you start with 1,1,2 ?

3.  Are looking for a DAX Calc column or a measure?