Forum Discussion

TimmK's avatar
TimmK
Icon for Helper IV rankHelper IV
3 years ago
Solved

How to Dynamically Subtract Variable Days?

For simplicity I have a table with the three columns "Order Key", "Date" and "Days".

 

I would like to use a DAX measure (or if this is not possible a calculated column) to subtract the days from the date for each row. Additionally, if the result of the calculation is a date that is on a Saturday another day should be subtracted and if it is a Sunday two additional days should be subtracted. How can I do this?

 

 

  • johnt75's avatar
    johnt75
    3 years ago

    You could create a measure like

    Adjusted date =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR ReferenceDays =
    SELECTEDVALUE ( 'Table'[Days] )
VAR BaseDate = ReferenceDate - ReferenceDays
VAR Result =
    SWITCH ( WEEKDAY ( BaseDate ), 1, BaseDate - 2, 7, BaseDate - 1, BaseDate )
RETURN
    Result

9 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could create a measure like

    Adjusted date =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR ReferenceDays =
    SELECTEDVALUE ( 'Table'[Days] )
VAR BaseDate = ReferenceDate - ReferenceDays
VAR Result =
    SWITCH ( WEEKDAY ( BaseDate ), 1, BaseDate - 2, 7, BaseDate - 1, BaseDate )
RETURN
    Result
    • TimmK's avatar
      TimmK
      Icon for Helper IV rankHelper IV
      3 years ago

      johnt75 Ah, actually the last part unfortunately does not work as intended. For example, let us say the reference date is November 21, 2022 (Monday) and 3 days should be subtracted. Then based on your measure the result would be November 18, 2022 (Friday). However, it actually should be November 16, 2022 (Wednesday) because it should be 3 workdays earlier, which means November 21, 2022 - 2 weekend days - 3 workdays.

       

      Could you please help with a solution?

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        Try

        Adjusted date =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR ReferenceDays =
    SELECTEDVALUE ( 'Table'[Days] )
VAR BaseDate = ReferenceDate - ReferenceDays
VAR WorkingDaysDiff =
    NETWORKDAYS ( BaseDate, ReferenceDate )
VAR Result = BaseDate - ( ReferenceDays - WorkingDaysDiff )
RETURN
    Result
  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago
    Try this:
     
    Adjusted date =
    VAR ReferenceDate =
        SELECTEDVALUE ( 'Table'[Date] )
    VAR ReferenceDays =
        SELECTEDVALUE ( 'Table'[Days] )
    VAR BaseDate = ReferenceDate - ReferenceDays
    VAR Result =
        SWITCH ( 
        WEEKDAY ( BaseDate ), 
        1, BaseDate - 4,
        2, BaseDate - 5,
        3, BaseDate - 5,
        4, BaseDate - 5,
        BaseDate - 3
    )
    RETURN
        Result
    • TimmK's avatar
      TimmK
      Icon for Helper IV rankHelper IV
      3 years ago

      Thanks, but this results in a lot of additional rows and wrong dates. 😕