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

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.