difference between two days excluding weekends

[kinga]

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(ISNUMBER(tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date]),if(tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE(2018,10,1),1.0*[TU_1ActDelDate_CapturedDate]-[TU_1ACTDELDate]),BLANK()),BLANK()))

[kinga]

I proceeded to use the below - if(ISNUMBER(tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date]),if(ISNUMBER(tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date]),if(tCostAndCycles_TUDetails[TU_1ACTDELDate] >= DATE(2018,10,1),if(tCostAndCycles_TUDetails[TU_1ACTDELDate]<= DATE(2018,12,31),CALCULATE ( SUM ( 'Calendar Table'[IsWorkDay] ), DATESBETWEEN ( 'Calendar Table'[Date], 'tCostAndCycles_TUDetails'[TU_1ACTDELDate], 'tCostAndCycles_TUDetails'[TU_1ActDelDate_CapturedDate] ) ) - 1,BLANK()),BLANK()))))

[v-juanli-msft]

Hi @kinga

If your write the formula as 

IsWeekDay = IF(WEEKDAY(CalendarTable[Date];2)<=5,1)

then your formula in the last post should work

measure =
IF (
    ISNUMBER ( tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date] ),
    IF (
        ISNUMBER ( tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date] ),
        IF (
            ISNUMBER ( tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date] ),
            IF (
                tCostAndCycles_TUDetails[TU_1ACTDELDate] >= DATE ( 2018, 10, 1 ),
                IF (
                    tCostAndCycles_TUDetails[TU_1ACTDELDate] <= DATE ( 2018, 12, 31 ),
                    CALCULATE (
                        SUM ( 'Calendar Table'[IsWorkDay] ),
                        DATESBETWEEN (
                            'Calendar Table'[Date],
                            MAX('tCostAndCycles_TUDetails'[TU_1ACTDELDate]),
                            MAX('tCostAndCycles_TUDetails'[TU_1ActDelDate_CapturedDate])  
                                                       //IF yhis formula is a measure,and 
                                                      // if your original formula doesn't work, 
                                                     // please modify as this by adding "MAX" function
                        )
                    )
                        - 1,
                    BLANK ()
                ),
                BLANK ()
            )
        )
    )
)

Best Regards

Maggie

[v-juanli-msft]

Hi @kinga

Would you like 

tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE ( 2018, 10, 1 ) and not=weekends ?

or 

1.0 * [TU_1ActDelDate_CapturedDate]  (not include weekends)
                    - [TU_1ACTDELDate]  (not include weekends)   ?

TU 1 OnTime DEL Tracking =
IF (
    ISNUMBER ( tCostAndCycles_TUDetails[TU_1SCHDELDate].[Date] ),
    IF (
        ISNUMBER ( tCostAndCycles_TUDetails[TU_1ACTDELDate].[Date] ),
        IF (
            ISNUMBER ( tCostAndCycles_TUDetails[TU_1ActDelDate_CapturedDate].[Date] ),
            IF (
                tCostAndCycles_TUDetails[TU_1ACTDELDate] > DATE ( 2018, 10, 1 ),
                1.0 * [TU_1ActDelDate_CapturedDate]
                    - [TU_1ACTDELDate]
            ),
            BLANK ()
        ),
        BLANK ()
    )
)

Best Regards

Maggie

[kinga]

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]

@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