Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Please need help to exlude weekend from dates diff in the same column. The below formula works good for me and return the correct count of days.
Column = VAR temp = TOPN ( 1, FILTER ( 'Customer visit', 'Customer visit'[Customer id] = EARLIER ( 'Customer visit'[Customer id] ) && 'Customer visit'[DateOfVisit] < EARLIER ( 'Customer visit'[DateOfVisit] ) ), [DateOfVisit], DESC ) RETURN DATEDIFF ( MINX ( temp, [DateOfVisit] ), 'Customer visit'[DateOfVisit], DAY )
Thanks in advance
Solved! Go to Solution.
This is easier if you have a date dimension table so that you can iterate over days and count them like this:
[...]
RETURN
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] <= 'Customer visit'[DateOfVisit]
&& 'Calendar'[Date] > MINX ( temp, [DateOfVisit] )
&& WEEKDAY ( 'Calendar'[Date] ) IN { 2, 3, 4, 5, 6 }
)
)
This is easier if you have a date dimension table so that you can iterate over days and count them like this:
[...]
RETURN
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] <= 'Customer visit'[DateOfVisit]
&& 'Calendar'[Date] > MINX ( temp, [DateOfVisit] )
&& WEEKDAY ( 'Calendar'[Date] ) IN { 2, 3, 4, 5, 6 }
)
)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |