Sum for Rolling 30 Day Window

[amelia]

Hi all, I hope you would be able to help with this. I am creating a dashboard with the week dates as rows, and cumulative sales and sales as 2 columns (as shown in the image below). The cumulative sum is for the last 30 days only , so RSUM() would not be applicable here. 

I tried doing this:  

SUM(([Sales], RANGE ( PREV ([Days in Date],30), CURRENT( [Days in Date]))))

to limit the range to the current date and 30 days ago. However, when I manually add the numbers up, it is wrong. Does this have to do with the fact that my rows are in weeks instead of days? When I set the rows to days instead of weeks, the values add up. Can anyone please advise on this? Thank you!

[Ophir_Buchman · Accepted solution]

Hi @amelia ,

You are correct -

• Your date measure uses a week granularity
• Your formula's measured value is using a day granularity

Meaning, that that the data will be cut to that week and only then be aggregated for the last 30 days (pretty much useless as you already filter most of these days).

There are different resolutions for this depending on your data, business questions, and filtering requirements. The easiest way to resolve this is if you can pre-calculate these numbers in the data model and present them as a simple chart. For example:

• You create a table of weekly sales and enrich the table with a 30-day history aggregation

[amelia]

Thank you for your help!!