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(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()))
 
Need Help

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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'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

         

  • 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