I have the usual montlhy sales. What I need is the trimester (quarter) sales with the beginning of the quarter being the month on the dimension.
Example: Dimension value: 201910 Measure value: 90 million
Explanation: 90 million would be the sum of the actual montlhy values regarding:
201910: 35 million
201911: 30 million
201912: 25 million
Example 2 (continuation): Dimension value: 201911 Measure value: 150 million
Explanation: 150 million would be the sum of the actual montlhy values regarding:
201911: 30 million
201912: 25 million
202001: 95 million
My (not so good) approach: I could (did not actually do this, I'm guessing a horrible performance) load my fact table another 2 times: changing only the reference date to 1 month ahead and then another one 2 months ahead.
My formula would be: sum(actual value)+sum(next month value)+sum(last month value)
I am sure that something could be done via set analysis. I tried a few formulas, Having the next 2 periods in the same line and applying [month]={"$([next month])"}. This is the general idea, but off course it did not work. I want to do the sum described above via set analysis.
I solved this in 2 ways: doing exactly what I said it was a not so good solution: loading the fact table putting addmonts(-1) and addmonths(-2). This created a necessity to remove the new 2 minimum dates miscreated. After that, it worked.
A more "classy" solution is using above: I forgot this could be used in graphs too! Using sum(valye)+above(sum(valye),1)+above(sum(valye),2).
Classy but it looses precision. That because when the measure is an avg, I need to do an rangeavg of the avgs. This is not as precise as doing the actual avg of all 3 months at once.
At last, I will try the solution you two suggested.
