Calculating Working Days in Power Bi DAX

References :

https://dax.guide/weekday/

 Step-1:

Create a calculated column in Date table.

"IsWorkingDay",NOT WEEKDAY( [Date] ) IN { 1,7 }




Step-2: Workingdays to Close

Working_Days_to_Close =

CALCULATE(

    COUNTROWS('fct_Date Table'),

    DATESBETWEEN('fct_Date Table'[Date], max('RefTable'[SubmittedDate]), max('RefTable'[ClosedDate]) - 1),

    'fct_Date Table'[IsWorkingDay] = TRUE,

    ALL('RefTable')

)

Step-3: Days to Close

Days to Close =

var submitdate=max('RefTable'[SubmittedDate])

var closeddate=max('RefTable'[ClosedDate])

RETURN CALCULATE(DATEDIFF(submitdate,closeddate,DAY),ALL('fct_Date Table'))

• ALL Function-> This function removes any filters that might be applied to the 'RefTable'. It ensures that the calculation considers all rows in the 'RefTable', regardless of any existing filters.