I am trying to create a table like you can see on the screenshot below.
Sales uses week_start and eow as a date range and Sales LY uses week_start_ly and eow (ly) as a date range. I want to have both calculations next to each other in a table, like on the screenshot above but I can't figure out the formula for Sales LY.
For example, I know that Sales (LY) for 17.10.2020 - 23.10.2020 = 6498 but my calculation has only blanks. How can I calculate Sales for LY and put it next to Sales TY?
My failed attempt:
SUM(
IF [Order Date] >= DATE(DATEADD('day', -364, [week_start]))
AND [Order Date] <= DATE(DATEADD('day', -358, [week_start]))
THEN[Sales] END
)
Attaching my workbook
good morning @Piotr Grobelny
there are a couple ways to get an answer - the one I would use is to fix an end point with a parameter ( see the second approach in https://jimdehner2.blogspot.com/2021/08/yoy-and-ytd-5-approaches.html ) but that will limit you to on week end
the other is pretty involved and would require creating a copy of the data and joining it to itself but offsetting the date by your 364 days - see the 4-5th examples in the same post - I do not recommend attempting that unless your are very advanced in joining data sets and your data is not complex -
see the attached for a brute force method using a parameter - i would use the example in step 2 of post and not what is shown here
jim
