topic Re: DateDiff between two dates excluding weekends and holidays in DAX Commands and Tips

DateDiff between two dates excluding weekends and holidays

Anonymous — Sat, 16 Oct 2021 23:47:39 GMT

I have a holidays table linked to date table and I have 2 calculated columns in the date table:

Job to Accep Elapsed TIme Days = (DATEDIFF('FreightForward v2'[JOB_BOOKING_DATETIME],'FreightForward v2'[ACCEPTANCE_DATETIME],DAY )) ---------------------------------------------------------------------------------------------- WorkingDay = IF(CONTAINSSTRING('Calendar Job Booking'[Holiday],"Anniversary") && 'Calendar Job Booking'[IsWorkingDay] = "true","Yes",IF('Calendar Job Booking'[Holiday]=BLANK() && 'Calendar Job Booking'[IsWorkingDay] = "true","Yes","No"))

For the 'elapsed time days' measure, how do i exclude weekends and holidays? Jihwan_Kim , any suggestions? Let me know if you require further information.

Re: DateDiff between two dates excluding weekends and holidays

Jihwan_Kim — Sun, 17 Oct 2021 06:26:55 GMT

Hi,

Could you share your sample pbix file by sharing the Onedrive link or any other type of link to your sample pbix file?

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Sun, 17 Oct 2021 07:44:24 GMT

sent you pm with link to file

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 20 Oct 2021 05:30:53 GMT

Jihwan_Kim- I understand you're unable to assist but I appreciate your help so far.

VahidDMare you able to assist?

Re: DateDiff between two dates excluding weekends and holidays

Icey — Wed, 20 Oct 2021 05:38:32 GMT

Hi Anonymous ,

Please check my reply in this similar thread: Work Hours disconsidering holidays and weekends. It should meet your requirements.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 20 Oct 2021 06:47:20 GMT

Icey- thanks for sharing that. I tried modifying measure to fit my requirements but I'm getting error due to some blanks as per screenshot. How do I account for blanks? Also for my requirement, I don't need min / max date and I need my result in days, not minutes.

Working Days (with Calendar Job Booking table) = VAR t1 = CALENDAR ( [JOB_BOOKING_DATETIME], 'FreightForward v2'[ACCEPTANCE_DATETIME] ) VAR t2 = FILTER ( ADDCOLUMNS ( t1, "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] ) ), [IsWorkDay_] ) VAR Days_ = COUNTROWS ( t2 ) VAR StartWorkingDateTime = CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 8, 0, 0 ), DATETIME ) VAR EndWorkingDateTime = CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 17, 0, 0 ), DATETIME ) VAR DateDiff_Start = IF ( StartWorkingDateTime < [JOB_BOOKING_DATETIME], DATEDIFF ( StartWorkingDateTime, [JOB_BOOKING_DATETIME], MINUTE ) ) VAR DateDiff_End = IF ( EndWorkingDateTime > [ACCEPTANCE_DATETIME], DATEDIFF ( [ACCEPTANCE_DATETIME], EndWorkingDateTime, MINUTE ) ) VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End RETURN WorkingMinutes / 60

Re: DateDiff between two dates excluding weekends and holidays

Icey — Wed, 20 Oct 2021 07:47:57 GMT

Hi Anonymous ,

I tried modifying measure to fit my requirements but I'm getting error due to some blanks as per screenshot. How do I account for blanks?

For the blanks, what is your calculation logic? Ignore it or use specify datetime?

Also for my requirement, I don't need min / max date and I need my result in days, not minutes.

Could this give what you want?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 20 Oct 2021 09:09:42 GMT

Thanks Icey but the formula is returning error.."failed to resolve name 'Days'. It is not a valid table, variable, or function name"

Regarding the blanks, just return null or blank

Re: DateDiff between two dates excluding weekends and holidays

Icey — Wed, 20 Oct 2021 09:13:20 GMT

Hi Anonymous ,

Sorry, I missed an underscore at the end. And again, for the blanks, what is your calculation logic? Ignore it or use specify datetime?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 20 Oct 2021 09:30:32 GMT

No prob, I should have picked up that simple syntax error even for a beginner myself:)

Ok its now returning error due to "start date cannot be later than the end date"

Sorry for not providing enough detail for the logic...the working days difference is a result of (Acceptance datetime - Job booking datetime i.e. Job booking always occurs first). If Acceptance datetime is earlier than Job booking datetime, do nothing (I presume it will just show negative value).

If job booking and/or acceptance datetime is blank, do nothing (which I presume will just show blank?)

Re: DateDiff between two dates excluding weekends and holidays

Icey — Wed, 20 Oct 2021 09:41:54 GMT

Hi Anonymous ,

Try this:

Working Days (with Calendar Job Booking table) = VAR t1 = IF ( NOT ( ISBLANK ( [JOB_BOOKING_DATETIME] ) ) && NOT ( ISBLANK ( [ACCEPTANCE_DATETIME] ) ), CALENDAR ( [JOB_BOOKING_DATETIME], 'FreightForward v2'[ACCEPTANCE_DATETIME] ) ) VAR t2 = FILTER ( ADDCOLUMNS ( t1, "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] ) ), [IsWorkDay_] ) VAR Days_ = COUNTROWS ( t2 ) RETURN Days_

If this doesn't work, in order to solve the problem for you faster, Could you create a sample .pbix file for me? Then I can create the expression you need directly. Please don't contain any sensitive information.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 20 Oct 2021 10:05:20 GMT

sent you pm with link

Re: DateDiff between two dates excluding weekends and holidays

Icey — Thu, 21 Oct 2021 08:55:57 GMT

Hi Anonymous ,

I create a column and a measure, please check if they are what you want:

Column:

Measure:

Measure - Working Days (with Calendar Job Booking table) = VAR t1 = CALENDAR ( MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ), IF ( ISBLANK ( MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ) ) || MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ) > MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ), MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ), MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ) ) ) VAR t2 = FILTER ( ADDCOLUMNS ( t1, "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] ) ), [IsWorkDay_] = "Yes" ) VAR Days_ = COUNTROWS ( t2 ) RETURN IF ( ISBLANK ( MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ) ) || MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ) > MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ), BLANK (), Days_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Thu, 21 Oct 2021 10:22:16 GMT

Thanks but I noticed some Working Day values are incorrect e.g. Customer no 91832861 Job datetime 5/10/2021 11:00pm, Acceptance datetime 6/10/2021 2:10pm Working Days = 2

Expected result should be 0 days (15hrs)?

Also can you please edit logic where if elapsed time between Job datetime and Acceptance datetime less than 24hours, return 0

Re: DateDiff between two dates excluding weekends and holidays

Icey — Fri, 22 Oct 2021 05:50:08 GMT

Hi Anonymous ,

In order to better check the calculation results, I modify the expression to just calculate the datediff of hours.

You can use TRUNC to truncates a number to an integer by removing the decimal, or fractional, part of the number, like TRUNC( DateDiff_Hour / 24 ).

Working Days (with Calendar Job Booking table) = VAR t1 = CALENDAR ( [JOB_BOOKING_DATETIME], IF ( ISBLANK ( [ACCEPTANCE_DATETIME] ) || [JOB_BOOKING_DATETIME] > [ACCEPTANCE_DATETIME], [JOB_BOOKING_DATETIME], [ACCEPTANCE_DATETIME] ) ) VAR t2 = FILTER ( ADDCOLUMNS ( t1, "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] ) ), [IsWorkDay_] = "Yes" ) VAR Days_ = COUNTROWS ( t2 ) - 1 VAR StartWorkingDateTime = MINX ( t2, [Date] ) VAR EndWorkingDateTime = MAXX ( t2, [Date] ) VAR JOB_BOOKING_DATE = DATE ( YEAR ( [JOB_BOOKING_DATETIME] ), MONTH ( [JOB_BOOKING_DATETIME] ), DAY ( [JOB_BOOKING_DATETIME] ) ) VAR ACCEPTANCE_DATE = DATE ( YEAR ( [ACCEPTANCE_DATETIME] ), MONTH ( [ACCEPTANCE_DATETIME] ), DAY ( [ACCEPTANCE_DATETIME] ) ) VAR DateDiff_Start = IF ( StartWorkingDateTime = JOB_BOOKING_DATE, DATEDIFF ( StartWorkingDateTime, [JOB_BOOKING_DATETIME], HOUR ) ) VAR DateDiff_End = IF ( EndWorkingDateTime = ACCEPTANCE_DATE, DATEDIFF ( EndWorkingDateTime, [ACCEPTANCE_DATETIME], HOUR ) ) VAR DateDiff_Hour = IF ( ISBLANK ( [ACCEPTANCE_DATETIME] ) || [JOB_BOOKING_DATETIME] > [ACCEPTANCE_DATETIME], BLANK (), Days_ * 24 - DateDiff_Start + DateDiff_End ) RETURN DateDiff_Hour

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Fri, 22 Oct 2021 10:46:30 GMT

Iceythank you, it seems to be working now. Appreciate your time and help with this.

I have one more request please if possible, how do I group 'working days' similar to existing visual as per screenshot below (refer to file shared with you) i.e. where total job bookings >= than 10 days, show 10+?

Note: the top visual using datediff formula including weekends/holidays. Bottom visual using your new calculated column formula

Also interesting to note the significant difference in results using your formula - everything same except I replaced axis with your formula. The decrease is expected when excluding weekends/holidays, but not sure if it should be that significant...

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Sun, 24 Oct 2021 04:07:41 GMT

Jihwan_Kim- are you able to assist please on how to incorporate the 'Working Days' custom column measure with the visual i.e. group by days?

Re: DateDiff between two dates excluding weekends and holidays

Icey — Tue, 26 Oct 2021 08:28:52 GMT

Hi Anonymous ,

You can just create another column like so:

Working Days Group = SWITCH ( [Working Days] < 10, CONVERT ( [Working Days], STRING ), "10+" )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DateDiff between two dates excluding weekends and holidays

Anonymous — Wed, 27 Oct 2021 10:03:01 GMT

Hi Icey , I'm getting the following error when applying your custom column