Forum Discussion

Amine008's avatar
Amine008
Frequent Visitor
4 years ago
Solved

Calculate DATEDIFF Same column without Weekends

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

DATEDIFF
DAX
Networkdays
weekdays
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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