Forum Discussion

burnt_fingers's avatar
burnt_fingers
Advocate I
6 years ago

Calculating Business Days between 2 dates.

Hi All,    So i have a calendar table that looks like this :   Then in other table i have 2 dates between i need to calculate business day diff. I'm using following formula :   Yet,...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi  burnt_fingers ,

 

Create a measure as below:

Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0

And you will see:

If you wanna the same dates return 1 instead of 0,then modify the measure as below:

Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>=MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0

For details,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
burnt_fingers's avatar
burnt_fingers
Advocate I
6 years ago

Hi,

  Thanks for your inputs but i was able to get my results slightly reworking Greg_Deckler foruma.

It looks like this now :

DaysDiff = 
Var Calendar1 = CALENDAR(DURATION_ERRORS[Creation],IF(ISBLANK(DURATION_ERRORS[Correction]),DURATION_ERRORS[Creation],DURATION_ERRORS[Correction]))
VAR Calendar2 = ADDCOLUMNS(
                    Calendar1,"wkd",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[wkd]<6),[Date])-1

 

yet im still wondering why the original formula did not work for me.