Assuming you have some sort of calendar in your data model, you could add a YearMonth field to it and then create a new calendar (let's call it RollingCalendar for now). In that calendar you'll create a field called something like RollingYearMonth and link the original YearMonths to it. So for example, the RollingYearMonth value 201206 should be linked to 12 different YearMonth values (201107, 201108, 201109... 201206).
If you then use this new RollingYearMonth as a dimension in your chart, you can calculate R12 averages, sums etc. pretty easily.
Hope this helps!
If the goal is just rolling amount there is a way without making changes in data model.
You could use rangesum(above(Sum(Value),0,12)).
The only issue is that with two and more dimensions (date, Manager) this expression works only in a pivot or straight table.
For charts the solution is to use explicit aggregation:
aggr(rangesum(above(Sum([Weighted amount in SEK]), 0, 12)), [Account Manager], Expected_Decision_Date)
If you transform your chart into a table you will see.
There is no dimension values with dates more than 2012-05 for 'blue'.
It's a common question in the community.
I suggest you to add the nulls for each Date-Manager combination. It's not so hard and not so much data:
drop table AllCombinations;
Sorry, I am a complete nb, so I would not get this to work.
This is how my script looks. Would it still be possible to use your suggestion by renaming Expected_Decision_Date to CalendarDate and [Account Manager] to AccountManager?
Or do I need to include every field?
Date as CalendarDate,
I assumed that your table with data was named 'Data'.
You have to name your table so that you could concatenate the values to it.
Just add the name (for example Data) before your load followed by semicolon.
Then just append the code that I've provided above after your load statement.
I suggest you to look through the reference manual (the script part) for better understanding of what you're doing.