First working day-Monday of 'current year - 1' if it's weekend

Question

Hello everyone,

In one of our analysis we have comparison of a date chosen in filter (YYYY-MM-DD) and same date previous year. The problem arises if that day previous year falls into weekend (Saturday or Sunday) because there's no data. What we need is a function or some kind of TIMESTAMPADD (if it exists) that would return the first working day i.e. first Monday after that date previous year (date chosen in filter - 1year), meaning if that date previous year was f.e. saturday then add 2 days and return monday because otherwise there's no data and column for the previous year is empty...Is there any solution for this ? Thanks in advance

P.S.: Current function for that column is FILTER("T1"."C1" USING ("T1"."C2" = TIMESTAMPADD(SQL_TSI_YEAR, - 1, cast(@{pvar_date}{date '2020-09-01'} as date)))), but when, in this case,  2019-09-01 is Saturday or Sunday we get no data...

Kind regards,

Mihajlo

Mihajlo · Answer

Hello Christian,

Thank you ! You are right, we solved in on DB level.

BR, Mihajlo

Mihajlo · Answer

Hi Joel,

Thanks for the advice, we followed it and applied as a solution

BR, Mihajlo

[Deleted User] · Answer

+1 to Joel. Forget trying to do that in a formula for one simple reasons which should be obvious to anyone working with any kind of data:

Calculation effort. If you put that into a formula, then that logic gets executed EVERY time the column is called for EVERY row within that query. There is hardly anything that's less efficient and more wasteful than that.

Not even starting with "What about leap years?" or the 500 other questions tied to date logic. Build a correct calendar that represents YOUR company's date logic.