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.

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])))