How to compare data between two custom date ranges, or Year over Year

Let’s say we have sales orders, 1 row per order, and we want to compare sales between, say, Feb 12 through Feb 25th of 2022, for the same period in 2021. [Sale Amt] is the base column with the sales amount per order, [order_date] is the date of the order. Here are the steps:

  1. Create 2 date picker parameters: prm_start_date and prm_end_date
  2. Create 2 new calculated fields:

[Sale Amt if in this year’s range]:

if(between([order_date],[prm_start_date], [prm_end_date]) , [Sale Amt], 0 )

[Sale Amt if in previous year’s range]:

if(between([order_date],DateAdd(“year”,-1,[prm_start_date]), DateAdd(“year”,-1,[prm_end_date])) , [Sale Amt], 0 )

  1. Create 2 summary fields (in the summary area at the bottom):

[Sum Sales for period in current year]:

sum([Sale Amt if in this year’s range])

[Sum Sales for period in previous year]

sum([Sale Amt if in previous year’s range])

1 Like