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.

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.