Counting working days in DAX months

[DimaMD]

Hello community, how can you calculate the total number of working days in a month, the condition is that when selecting dates in the slicer, the number of days per month does not change
For example, if I choose a date from 01.01.2023 to 01.30.2023, I should see 22 working days
Thanks for the tip

[DimaMD · Accepted solution]

Hi, I achieved the desired result on my own, in the calendar table I added a column "MonthYer"

CONCATENATE(YEAR([Date]), CONCATENATE("-", FORMAT([Date],"mm")))

Next, I calculated the Total number of working days

Total Working Days = 
VAR Year = YEAR( [МісяцьРік] )
VAR Month = MONTH( [МісяцьРік] )
VAR DatesInMonth = GENERATESERIES( [МісяцьРік], DATE( Year, Month + 1, 1 ) - 1, 1 )
RETURN SUMX(
    DatesInMonth,
    IF( WEEKDAY( [Value] ) IN { 1, 7 }, 0, 1 )
)

And in his last dream he calculated the average for a month, which is 22 days

workdates = AVERAGE('Calendar'[Total Working Days])

[DimaMD]

@FreemanZ  I tried this measure, but when changing the selected period in the stacker, the amount of working days changes

Total Working Days = 
CALCULATE(
    COUNTROWS('Calendar'),
    'Calendar'[IsWorkingDay] = TRUE(),
    ALL('Calendar'),
    DATESMTD('Calendar'[Date])
)

[FreemanZ]

hi @DimaMD 

it seems 2023/1/31 is excluded by the date slicer, so comes 21 instead of 22.

[DimaMD]

@FreemanZ Hi, Yes, but my desired result should be 22, I need to ignore the selected period in the slicer

[FreemanZ]

hi @DimaMD 

tried to 

1) create two unrelated tables like:

Slicer = CALENDAR(DATE(2023,1,1), DATE(2023,2,28))

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2023,2,28)),
    "IsWorkDay", 
    VAR _value = WEEKDAY([Date],2)
    RETURN
    IF(
    NOT _value IN {6,7},
    "Yes", "No"
    )
)

2) plot a slicer with slicer[date] and a card visual with a measure like:

Measure = 
COUNTROWS(
    FILTER(
        DateTable,
        DateTable[IsWorkDay]="Yes"
            &&FORMAT([Date], "YYYYMM")=FORMAT(SELECTEDVALUE(Slicer[Date]), "YYYYMM")
    )
)

it worked like:

[DimaMD]

Hello @FreemanZ , for some reason these measures do not work for me slicer "within" and is it possible to calculate this without using 2 date tables?

[johnt75]

You can use the NETWORKDAYS function. https://dax.guide/networkdays/