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
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's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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