I am looking how I can insert a function into a revenue column to retrieve revenue figures from last year, but specifcally year to date so last year to date.
I know if I dont want to "hard code" a certain year I use the function year(current_date) and if I want last years figures I use the function year(current_date)-1.
However, how can I do this for monthly basis? Meaning what is the function if I want to show last year to date (LYTD)? The year(current_date)-1 returns the full year data, however what if I want to show only last year up to February because we are only up to February for this year?
I know I can use the timestampadd function to retrieve an x period of time in the past, however it still needs to be updated with either -1, -2, -7 etc whatever I want to show. Which means that every month I would still need to go into the column and adjust the function to incorporate (or in this case reduce the digit by 1) another month i.e. -12 then -11 then -10 months from current date etc
I am looking for a function that would do that automatically thats why I dont think I can use a timestampadd.
Would appreciate any input or if anybody has this worked out!