Forum Discussion

kinga's avatar
kinga
Helper I
7 years ago

difference between two days excluding weekends

Hello, I have the below formula. I am looking to get this to exclude weekends. Any suggestions?   TU 1 OnTime DEL Tracking = if(ISNUMBER(tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date]),if(ISNUMBE...
Need Help
kinga's avatar
kinga
Helper I
7 years ago

Hello,

 

For any records that have a TU_1ACTDELDate > DATE (2018, 10, 1 ), it would need to calculate the difference in days between TU_1ActDelDate_CapturedDate and TU_1ACTDELDate, excluding weekends.

 

Does this help to clarify?

 

Vvelarde's avatar
Vvelarde
Community Champion
7 years ago

kinga

 

Hi, This is a sample: (You need to adapt to your needs)

 

In your calendar table add a calculated column:

 

IsWeekend = IF(WEEKDAY(CalendarTable[Date];2)>5,"Y","N")

Create a measure:

 

DiffDaysWithoutWeekends =
VAR _Start =
    SELECTEDVALUE ( Table1[StartDate] )
VAR _End =
    SELECTEDVALUE ( Table1[EndDate] )
RETURN
    IF (
        HASONEVALUE ( Table1[ID] ),
        CALCULATE (
            COUNT ( CalendarTable[Date] ),
            FILTER (
                ALL ( CalendarTable ),
                CalendarTable[Date] >= _Start
                    && CalendarTable[Date] <= _End
                    && CalendarTable[IsWeekend] = "N"
            )
        )
    )

Regards

 

Victor