Forum Discussion

richardmayo's avatar
richardmayo
Helper II
6 years ago
Solved

DATEDIFF excluding weekends

Ive had a look at existing threads but none seem to be as simple as my scenario.   This is my formula in a measure Turnaround (Days) = DATEDIFF('Jobs'[date_entered],'Jobs'[exported_date],DAY)  ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi @richardmayo,

    You can follow the below steps to get the number of days between date_entered and exported_date:

    1. Create one calendar table with normal date if your data model still not have any date table   

    2. Add one calculated column on table Jobs with the below formula:        

    Turnaround (Days) = CALCULATE(COUNTROWS('Calendar'),filter('Calendar',WEEKDAY('Calendar'[Date],2)<6),DATESBETWEEN('Calendar'[Date],'Jobs'[date_entered],'Jobs'[exported_date]))

    Best Regards

    Rena

JuNu_MDJ's avatar
JuNu_MDJ
New Member
3 years ago

Please replace CMS_Detail[Reported_Date] and CMS_Detail[Completed_Date] as your start date and end date.

 

Dates_between =
VAR TEMP_CALENDAR =
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                CMS_Detail[Reported_Date],
                COALESCE ( CMS_Detail[Completed_Date], TODAY () )
            ),
            "DAY_NAME", WEEKDAY ( [Date], 1 )
        ),
        [DAY_NAME] < 6
    )
RETURN
    COUNTROWS ( TEMP_CALENDAR )

Ping me, in any case, Junaise PT 

 

Did I answer your question? Mark my post as a solution!