Difference between two dates with excluding weekends.

[VenkataRavi]

Dear Team,

Please help me solve for construct below query.

ID	Status	Start Date	End Date	No: of Days
201	Done	10/03/2022	10/06/2022	3
205	Approved	10/04/2022		9(when end date is blank we need to consider Today()-Start Date.)
206	Deployed	10/04/2022		9(when end date is blank we need to consider Today()-Start Date.)
207	Done	09/26/2022	09/29/2022	3

Please guide me how to write DAX fuction for above query.

Advance Thanks,

Venkata.

[Anonymous · Accepted solution]

Hi @VenkataRavi ,

Assum that there is one date dimension table in your model, you can create a calculated column as below to get the number of days between two dates. Please find the details in the attachment.

No.of Days =
VAR _enddate =
    IF ( ISBLANK ( 'Table'[End Date] ), TODAY (), 'Table'[End Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Date' ),
        DATESBETWEEN ( 'Date'[Date], 'Table'[Start Date], _enddate ),
        FILTER ( 'Date', WEEKDAY ( 'Date'[Date], 2 ) < 6 )
    )

In addition, you can refer the following blog to get it by DAX or Power Query method...

Calculate Workdays Between Two Dates In Power BI

Best Regards

[lbendlin]

Please read about the NETWORKDAYS() function.