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