Business Days Calculation

[Anonymous]

Hello!

I know that in the past there wasn't really a DAX formula to create the difference in business days between two dates. I was just wondering if in recent updates, maybe there's an easy way to do it? 

Thanks,

[v-henryk-mstf]

Hi @Anonymous ,

According to your request, I did the following test: first add the Date column to slicer to get the two dates you want, then create a Result measure, VAR min and VAR max parameters to get the corresponding value of the selected date, and finally return result.

Here is the sample .pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Still no out of the box formula

As a new column

Business days = SWITCH(TRUE(), NOT(ISBLANK(Employee[Start Date])) && NOT(ISBLANK(Employee[End Date])) , COUNTROWS(FILTER(
ADDCOLUMNS(CALENDAR([Start Date],[End Date]),"Weekday",WEEKDAY([Date])),[Weekday]
<6)))

Or Check the 2nd page of the attached file for measure

or refer to this blog - https://curbal.com/blog/glossary/networkdays-dax

[littlemojopuppy]

There is.  In your date table add a column for weekday (easily done using the WEEKDAY() function).  Including/excluding holidays is a little more difficult...

In all the measures you write you'll have to include a FILTER() for where weekday <> Sunday and <>Saturday (refer to the documentation for the weekday function for the exact values to use because it will depend on when what day of week you want to be first).

If you can provide some sample data I can write you a sample measure...