Forum Discussion
Difference between two dates with excluding weekends.
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.
Hi Anonymous ,
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
2 Replies
Please read about the NETWORKDAYS() function.
Hi Anonymous ,
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
