Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've read through various topics surround this however I haven't been able to reproduce or get this working.
I need to calculate the date difference between two dates excluding a weekend.
I have two tables with a relation ship between Date & Resolved Date.
How do i calculate difference between Last Resolved Date & Submit date, excluding weekends.
DimDate
ResolvedINC
Solved! Go to Solution.
Hi @Anonymous ,
If you want to count work days, create a measure like so:
Measure =
CALCULATE (
COUNT ( DimDate[Date] ) + 0,
FILTER (
DimDate,
[Is Weekend] = 0
&& [Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& [Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
If you want to calculate the specific time, try to create a measure like so:
Measure 2 =
VAR MinWeekend =
CALCULATE (
MIN ( DimDate[Date] ),
FILTER (
DimDate,
DimDate[Is Weekend] = 1
&& DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
VAR MaxWeekend =
CALCULATE (
MAX ( DimDate[Date] ),
FILTER (
DimDate,
DimDate[Is Weekend] = 1
&& DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
VAR MaxWeekend_ =
IF ( MaxWeekend <> BLANK (), MaxWeekend + 1 )
VAR DateDiff1 =
DATEDIFF (
MAX ( ResolvedINC[Submit date] ),
MAX ( ResolvedINC[Resolved Date] ),
SECOND
)
VAR DateDiff2 =
DATEDIFF ( MAX ( ResolvedINC[Submit date] ), MinWeekend, SECOND )
+ DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff3 =
DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff_ =
IF (
MinWeekend = BLANK (),
DateDiff1,
IF (
MAX ( ResolvedINC[Submit date] ) < MinWeekend
&& MAX ( ResolvedINC[Resolved Date] ) > MaxWeekend_,
DateDiff2,
IF (
MAX ( ResolvedINC[Submit date] ) >= MinWeekend
&& MAX ( ResolvedINC[Submit date] ) <= MaxWeekend_,
DateDiff3
)
)
)
VAR Days =
TRUNC ( DateDiff_ / 24 / 3600 )
VAR Hours =
TRUNC ( ( DateDiff_ - Days * 24 * 3600 ) / 3600 )
VAR Minutes =
TRUNC ( ( DateDiff_ - Hours * 3600 - Days * 24 * 3600 ) / 60 )
VAR Seconds = DateDiff_ - Minutes * 60 - Hours * 3600 - Days * 24 * 3600
VAR Result =
Days & ":"
& FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
Result
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you want to count work days, create a measure like so:
Measure =
CALCULATE (
COUNT ( DimDate[Date] ) + 0,
FILTER (
DimDate,
[Is Weekend] = 0
&& [Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& [Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
If you want to calculate the specific time, try to create a measure like so:
Measure 2 =
VAR MinWeekend =
CALCULATE (
MIN ( DimDate[Date] ),
FILTER (
DimDate,
DimDate[Is Weekend] = 1
&& DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
VAR MaxWeekend =
CALCULATE (
MAX ( DimDate[Date] ),
FILTER (
DimDate,
DimDate[Is Weekend] = 1
&& DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
&& DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
)
)
VAR MaxWeekend_ =
IF ( MaxWeekend <> BLANK (), MaxWeekend + 1 )
VAR DateDiff1 =
DATEDIFF (
MAX ( ResolvedINC[Submit date] ),
MAX ( ResolvedINC[Resolved Date] ),
SECOND
)
VAR DateDiff2 =
DATEDIFF ( MAX ( ResolvedINC[Submit date] ), MinWeekend, SECOND )
+ DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff3 =
DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff_ =
IF (
MinWeekend = BLANK (),
DateDiff1,
IF (
MAX ( ResolvedINC[Submit date] ) < MinWeekend
&& MAX ( ResolvedINC[Resolved Date] ) > MaxWeekend_,
DateDiff2,
IF (
MAX ( ResolvedINC[Submit date] ) >= MinWeekend
&& MAX ( ResolvedINC[Submit date] ) <= MaxWeekend_,
DateDiff3
)
)
)
VAR Days =
TRUNC ( DateDiff_ / 24 / 3600 )
VAR Hours =
TRUNC ( ( DateDiff_ - Days * 24 * 3600 ) / 3600 )
VAR Minutes =
TRUNC ( ( DateDiff_ - Hours * 3600 - Days * 24 * 3600 ) / 60 )
VAR Seconds = DateDiff_ - Minutes * 60 - Hours * 3600 - Days * 24 * 3600
VAR Result =
Days & ":"
& FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
RETURN
Result
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Refer to this file. It has date diff between two dates across two tables. Check For Page 2/Workday diff
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
I am struggling with the same issue and i did try mentioned measures and other DAX but no luck here i am sharing with you a sample of the report it would be a great help if you can help
https://1drv.ms/u/s!Aj7YLBYfrzPkil3VcmfrgIPNio5c?e=Lat0Jo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |