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, my __Days_Diff column only get 1's and few occasional 0's, which is obviously wrong

 

Can any1 advise what I'm doing wrong here ?

 

Best regards.

Need Help

4 Replies

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