Spotfire Rolling YTD period calculation

Question

I would like to calculate rolling YTD for previous year in Spotfire Cross Table. Example: I have YTD for this year (Jan-Jul 2019), so I need to calculate YTD for Jan-Jul 2018.

I've already calculated YTD for previous year, but the formula works just one month and then I have to adjust it manually again.

    Sum(If(([Date]>=Max(DateAdd("mm",-17,[Date]))) and ([Date] <=Max(DateAdd("mm",-12,[Date]))),[Sales]))

Could you please give me some advice how to build the formula so I don't have to adjust it every month? Thank you very much.

Answer 1

This should achieve what you want, if I understood it well :

Sum(If(([Date]>=Date(Year([Date]) - 1,1,1)) and ([Date]<=Max(DateAdd("mm",-12,[Date]))),[Sales]))

It takes the sum of sales from the 1st of january of previous year until the current day and month of data of previous year.