Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate difference between two dates excluding weekends using a date table?

This is how I got my working days calculations 

 

I added the working days calculations to a "date" table in my Power BI workbook. However, I do not understand how to use the working days column I now have to determine the difference between two dates. 

Here's my table:

 

 

 

Here's an example of my Date table:

 

 

 

 

Since I know the working days (by 1) and the non-working days (0), how can I add another column or a measure (Business Days Elapsed) to see how many working days is between start date and end date? I do not understand this part. 

Need Help
Tips and Tricks
  • calerof's avatar
    calerof
    5 years ago

    Anonymous 

    You could use this code:

    WorkingDaysDiff = 
VAR BlankDate = ISBLANK(Weekday5[Start Date]) || ISBLANK(Weekday5[End Date])
RETURN
IF(BlankDate, 0,
    CALCULATE(
        COUNTROWS ( 'Calendar'),
        DATESBETWEEN ( 'Calendar'[Date],  'Weekday5'[Start Date], 'Weekday5'[End Date] ),
        'Calendar'["IsWorkingDay"] = TRUE,
        ALL ( 'Weekday5' )
    )
)

    And you'll get this result:

    Hope it helps.

    Regards,

    Fernando

     

    P.S. If it helped, please consider liking the post and marking it as an answer.

4 Replies

  • calerof's avatar
    calerof
    Impactful Individual
    5 years ago

    Hi Anonymous ,

    It's time for you to start your long journey to meet the great SQLBI guys here.

    Use this code in a new column in your table:

    WorkingDaysDiff = 
CALCULATE(
    COUNTROWS ( 'Calendar'),
    DATESBETWEEN ( 'Calendar'[Date],  'Weekday'[Start Date], 'Weekday'[End Date] ),
    'Calendar'["IsWorkingDay"] = TRUE,
    ALL ( 'Weekday' )
)

    You get this:

    Regards,

    Fernando

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      calerof Thank you. This solution worked, but I see one error. If there is no date for either the Weekday[Start Date] or Weekday[End Date], such as if we have no data for the order dates, it sets WorkingDaysDiff to the maximum possible days in the calendar. Since mine goes back 2 years, I have thousands of rows set to 783 now. 

       

      How can I default them to 0 if there's no data present? 

      • calerof's avatar
        calerof
        Impactful Individual
        5 years ago

        Anonymous 

        You could use this code:

        WorkingDaysDiff = 
VAR BlankDate = ISBLANK(Weekday5[Start Date]) || ISBLANK(Weekday5[End Date])
RETURN
IF(BlankDate, 0,
    CALCULATE(
        COUNTROWS ( 'Calendar'),
        DATESBETWEEN ( 'Calendar'[Date],  'Weekday5'[Start Date], 'Weekday5'[End Date] ),
        'Calendar'["IsWorkingDay"] = TRUE,
        ALL ( 'Weekday5' )
    )
)

        And you'll get this result:

        Hope it helps.

        Regards,

        Fernando

         

        P.S. If it helped, please consider liking the post and marking it as an answer.

  • mhossain's avatar
    mhossain
    Solution Sage
    5 years ago

    Anonymous 

     

    Try this measure: make sure you change the Table/column names as per your table

     

    Business_Days_Elapsed? =
     
    SUMX('Table',DATEDIFF('Table'[Start Date],'Table'[End Date],DAY))
    -
    CALCULATE(COUNT(wDay[Working day]),wDay[Working day]=0,
    FILTER(wDay,wDay[Date]>=MAX('Table'[Start Date])),FILTER(wDay,wDay[Date]<=MAX('Table'[End Date])))