I am trying to replicate existing reports which include MTD and YTD measures. I don’t want the YTD as per now but for each and every period in the past.
For example let’s say we are reporting the number of sales by week (from a sales daily model):
Week | Sales
1 | 2234
2 | 3443
3 | 2653
What I need now is also the running YTD for each week like:
Week | Sales | Sales_YTD
1 | 2234 | 2234
2 | 3443 | 5677
3 | 2653 | 8330
I tried joining my sales view in to another sales view but didn’t succeed, I thought maybe derived tables? I’ve seen many posts about YTD but none really covers what I need.
In summary: I should be able to select one date dimension (day, week, month) and then have two measures: Sales and Sales YTD (Sales YTD would be the sum of sales from the 1st Jan to the end of the period selected in the date dimension)
It’s like a left join from the sales view in to another derived sales YTD view where the sale date would be between 1st Jan and the first view’s dimension end date).
I hope this is clear and that there is a genius out there with a creative solution.