Forum Discussion

TimmK's avatar
TimmK
Helper 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 t...
  • 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
TimmK's avatar
TimmK
Helper 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
Super 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
  • TimmK's avatar
    TimmK
    Helper IV
    3 years ago

    This is what I get with the updated measure:

     

    Almost there. If the date is 2022-11-16 (WED) and 2 days should be subtracted then the result should be 2022-11-14 (MON) instead of 2022-11-15 (TUE). It seems that addtional 1 day needs to be subtracted for each row.

     

    Also, for example if the date is 2022-11-16 (WED) and 3 days should be subtracted then the result should be 2022-11-11 (FRI) instead of 2022-11-13 (SUN).

     

    Additional question: How can I remove the 30.12.1899 from the total?

     

    Thank you very much for your great help.

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      I think maybe a different approach. Add a column to your date table, Is Working Day, which returns 1 if the day is not a weekend. Then you can create a measure like

      Adjusted date =
IF (
    ISINSCOPE ( 'Table'[Date] ),
    VAR ReferenceDate =
        SELECTEDVALUE ( 'Table'[Date] )
    VAR ReferenceDays =
        SELECTEDVALUE ( 'Table'[Days] )
    VAR WorkingDates =
        CALCULATETABLE (
            TOPN ( ReferenceDays, 'Date', 'Date'[Date], DESC ),
            'Date'[Date] < ReferenceDate,
            'Date'[Is Working Day] = 1
        )
    RETURN
        MINX ( WorkingDates, 'Date'[Date] )
)
      • TimmK's avatar
        TimmK
        Helper IV
        3 years ago

        This seems to have the correct result. Yet, when I add the measure to the table it loads a very long time. In another table (that has some more rows, 15 to be exact) it does not work at all, after a while I receive the error message "There's not enough memory to complete this operation.".

         

        Is there any possibility to improve the performance or maybe another solution?