Error 12 Month Rolling SUM

[mmazzalay]

Hey there!
I'm trying to calculate the sum of Sales 12 month rolling back. I found a formula, but it is not working well, it has an error, it's not including Feb-2019 sales.

Rolling 12 meses Sales = CALCULATE([Sales];FILTER(ALL(TC[Fecha]);AND(TC[Fecha]<=MAX(Resultados[Fecha]);DATEADD(TC[Fecha];1;YEAR)>MAX(Resultados[Fecha]))))

Dif=15360177-14796582=563594 : February Sales

Any suggestion?
Thanks!

[jdbuchanan71 · Accepted solution]

Hello @mmazzalay 

I have seen odd behavior if you use time intelligence functions (DATEADD) without a calendar table that goes from Jan 1st - Dec 31st for all the years in your model.

See if this measure gives any different results

Rolling 12 meses Sales =
CALCULATE ( [Sales]; DATESINPERIOD ( TC[Fecha]; MAX ( TC[Fecha] ); -1; YEAR ) )

And if not, try adding a calendar table and linking it to your data table and using the dates there which would look something like this.

Rolling 12 meses Sales = CALCULATE([Sales];DATESINPERIOD(calendario[Fecha];MAX(calendario[Fecha]);-1,YEAR))

If this solves your issues please mark it as the solution so that others can find it easily. Kudos 👍 are nice too.
John

[mmazzalay]

I tried with the first option and it works perfectly!!!😁

Thanks! It was very helpfull!