topic Re: How to Dynamically Subtract Variable Days? in DAX Commands and Tips

How to Dynamically Subtract Variable Days?

TimmK — Thu, 10 Nov 2022 14:48:30 GMT

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?

Re: How to Dynamically Subtract Variable Days?

johnt75 — Thu, 10 Nov 2022 15:52:34 GMT

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

Re: How to Dynamically Subtract Variable Days?

TimmK — Mon, 14 Nov 2022 10:25:26 GMT

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?

Re: How to Dynamically Subtract Variable Days?

johnt75 — Mon, 14 Nov 2022 12:13:16 GMT

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

Re: How to Dynamically Subtract Variable Days?

FreemanZ — Mon, 14 Nov 2022 12:40:23 GMT

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

Re: How to Dynamically Subtract Variable Days?

TimmK — Mon, 14 Nov 2022 14:41:22 GMT

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.

Re: How to Dynamically Subtract Variable Days?

TimmK — Tue, 15 Nov 2022 08:57:38 GMT

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

Re: How to Dynamically Subtract Variable Days?

johnt75 — Tue, 15 Nov 2022 11:29:01 GMT

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] ) )

Re: How to Dynamically Subtract Variable Days?

TimmK — Tue, 15 Nov 2022 14:33:42 GMT

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?

Re: How to Dynamically Subtract Variable Days?

johnt75 — Tue, 15 Nov 2022 16:50:51 GMT

I think you could adapt the technique from https://www.youtube.com/watch?v=2HkBbqxBzF0