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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

burnt_fingers - See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

 

Also, I see you are a new member, Please check out this article: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • burnt_fingers's avatar
    burnt_fingers
    Advocate I
    6 years ago

    Greg_Deckler Thx for lots of usefull materials.

     Unfortunatly your solution does not work either, returns just '1' on each row.

     

    So my sample data looks like this 

    Custom_Creation	Custom_Corretion
4/16/2020	4/16/2020
4/14/2020	4/14/2020
7/9/2020	7/9/2020
7/8/2020	7/9/2020
7/1/2020	7/2/2020

     

    I would like to get difference in working days calculated between those two.

    As you can read above, that formula is almost direct copy paste from this forum. 

    What other requirements are there to make it work ?

    I do have active relationship between my __Calendar table and my RAW_Errors Table.

     

    Below is my __Calendar Table.

     

    Date	Year	Month	Day of the Week	Working Day	Date_Formatted	Date_Formatted_2
1/1/2020 12:00:00 AM	2020	1	4	1	01/01/2020	Wednesday, January 1, 2020
1/2/2020 12:00:00 AM	2020	1	5	1	01/02/2020	Thursday, January 2, 2020
1/3/2020 12:00:00 AM	2020	1	6	1	01/03/2020	Friday, January 3, 2020
1/4/2020 12:00:00 AM	2020	1	7	0	01/04/2020	Saturday, January 4, 2020
1/5/2020 12:00:00 AM	2020	1	1	0	01/05/2020	Sunday, January 5, 2020
1/6/2020 12:00:00 AM	2020	1	2	1	01/06/2020	Monday, January 6, 2020
1/7/2020 12:00:00 AM	2020	1	3	1	01/07/2020	Tuesday, January 7, 2020
1/8/2020 12:00:00 AM	2020	1	4	1	01/08/2020	Wednesday, January 8, 2020
1/9/2020 12:00:00 AM	2020	1	5	1	01/09/2020	Thursday, January 9, 2020
1/10/2020 12:00:00 AM	2020	1	6	1	01/10/2020	Friday, January 10, 2020
1/11/2020 12:00:00 AM	2020	1	7	0	01/11/2020	Saturday, January 11, 2020
1/12/2020 12:00:00 AM	2020	1	1	0	01/12/2020	Sunday, January 12, 2020

     

    • 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.