Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amine008
Frequent Visitor

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors