Forum Discussion
Error 12 Month Rolling SUM
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.
Dif=15360177-14796582=563594 : February Sales
Any suggestion?
Thanks!
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
2 Replies
- jdbuchanan71
Super User
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.
JohnI tried with the first option and it works perfectly!!!😁
Thanks! It was very helpfull!
