Calculate DATEDIFF Same column without Weekends

[Amine008]

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

[AlexisOlson · Accepted 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 }
        )
    )

[Amine008]

@AlexisOlson Thank you so much for your help here. Appreciate it 🙏