I am looking to create a date difference formula which will look back 90 days/160 days/365 days from each date and subtract the data from that date. Also if the look back day is a Sunday, then it should pick up the data for Monday else if the look back day is Saturday then the formula should pick the data for Friday. I need the final price difference based on the look back logic. The below example is for 90 days look back.
| Sr. | Product | New Price | Date | Look back 3 months | Look back day | Logic to be implemented | Price Difference |
| 1 | A | 10 | 5/1/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/01/2020 | Sunday | If look back day is Sunday, then take data for the next day i.e. Monday | 10-101 |
| 2 | A | 20 | 5/2/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/02/2020 | Monday | | 20-101 |
| 3 | A | 30 | 5/3/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/03/2020 | Tuesday | | 30-102 |
| 4 | A | 40 | 5/4/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/04/2020 | Wednesday | | 40-103 |
| 5 | A | 50 | 5/5/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/05/2020 | Thursday | | 50-104 |
| 6 | A | 60 | 5/6/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/06/2020 | Friday | | 60-105 |
| 7 | A | 70 | 5/7/2020 | DateTIMEAdd(ToDate([DATE]),-90,"days") = 02/07/2020 | Saturday | If Look back day is Saturday, then take data for the previous day i.e. Friday | 70-105 |
| | | | | | | | |
| Historical Data | | | | | | |
| Sr. | Product | Old Price | Date | Day | | | |
| 1 | A | 100 | 02/01/2020 | Sunday | | | |
| 1 | A | 101 | 02/02/2020 | Monday | | | |
| 1 | A | 102 | 02/03/2020 | Tuesday | | | |
| 1 | A | 103 | 02/04/2020 | Wednesday | | | |
| 1 | A | 104 | 02/05/2020 | Thursday | | | |
| 1 | A | 105 | 02/06/2020 | Friday | | | |
