Forum Discussion
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...
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
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
Thanks, but this results in a lot of additional rows and wrong dates. 😕