Forum Discussion
Another Running Total Question
OK turns out this was way easier than I was making it. Instead of messing with the formula and doing everything 'by force', there is a Quick Measure for Year to Date. I thought at the time I was playing with it that the YTD would only work for data in the current year, but it will actually work for multiple years. I was able to add a YTD version of the metric and it worked perfectly and gave me the exact graph I was looking for there.
Anonymous
Could you share your file?
Unfortunately no I can't, it has some sensitive data.
The format is something like this:
Customer Count Table:
| Territory | Customers | Year | Month | YearMon |
| A | 37,381.00 | 2017 | 12 | 201712 |
| B | 47,416.00 | 2017 | 12 | 201712 |
| C | 10,106.00 | 2017 | 12 | 201712 |
| D | 109,685.00 | 2017 | 12 | 201712 |
| E | 64,144.00 | 2017 | 12 | 201712 |
| F | 9,551.00 | 2017 | 12 | 201712 |
| G | 152,264.00 | 2017 | 12 | 201712 |
| H | 61,934.00 | 2017 | 12 | 201712 |
| I | 20,968.00 | 2017 | 12 | 201712 |
| J | 10,536.00 | 2017 | 12 | 201712 |
| K | 14,323.00 | 2017 | 12 | 201712 |
| L | 7,801.00 | 2017 | 12 | 201712 |
| M | 101,184.00 | 2017 | 12 | 201712 |
| N | 30,221.00 | 2017 | 12 | 201712 |
| O | 17,931.00 | 2017 | 12 | 201712 |
| P | 15,918.00 | 2017 | 12 | 201712 |
| Q | 83,562.00 | 2017 | 12 | 201712 |
| R | 49,529.00 | 2017 | 12 | 201712 |
| S | 2,844.00 | 2017 | 12 | 201712 |
| T | 21,332.00 | 2017 | 12 | 201712 |
| A | 37,452.00 | 2018 | 1 | 20181 |
| B | 47,483.00 | 2018 | 1 | 20181 |
| C | 10,108.00 | 2018 | 1 | 20181 |
| D | 109,898.00 | 2018 | 1 | 20181 |
| E | 64,600.00 | 2018 | 1 | 20181 |
| F | 9,548.00 | 2018 | 1 | 20181 |
| G | 152,249.00 | 2018 | 1 | 20181 |
| H | 61,999.00 | 2018 | 1 | 20181 |
| I | 20,983.00 | 2018 | 1 | 20181 |
| J | 10,530.00 | 2018 | 1 | 20181 |
| K | 14,327.00 | 2018 | 1 | 20181 |
| L | 7,804.00 | 2018 | 1 | 20181 |
| M | 105,975.00 | 2018 | 1 | 20181 |
| N | 30,184.00 | 2018 | 1 | 20181 |
| O | 18,001.00 | 2018 | 1 | 20181 |
| P | 15,908.00 | 2018 | 1 | 20181 |
| Q | 83,549.00 | 2018 | 1 | 20181 |
| R | 49,557.00 | 2018 | 1 | 20181 |
| S | 2,835.00 | 2018 | 1 | 20181 |
| T | 21,348.00 | 2018 | 1 | 20181 |
| A | 10,512.00 | 2018 | 3 | 20183 |
| B | 14,583.00 | 2018 | 3 | 20183 |
| C | 7,795.00 | 2018 | 3 | 20183 |
| D | 105,777.00 | 2018 | 3 | 20183 |
| E | 30,154.00 | 2018 | 3 | 20183 |
| F | 18,058.00 | 2018 | 3 | 20183 |
| G | 15,895.00 | 2018 | 3 | 20183 |
| H | 83,514.00 | 2018 | 3 | 20183 |
| I | 49,475.00 | 2018 | 3 | 20183 |
| J | 2,826.00 | 2018 | 3 | 20183 |
| K | 21,353.00 | 2018 | 3 | 20183 |
| L | 37,498.00 | 2018 | 3 | 20183 |
| M | 47,551.00 | 2018 | 3 | 20183 |
| N | 10,104.00 | 2018 | 3 | 20183 |
| O | 109,784.00 | 2018 | 3 | 20183 |
| P | 64,638.00 | 2018 | 3 | 20183 |
| Q | 9,586.00 | 2018 | 3 | 20183 |
| R | 152,341.00 | 2018 | 3 | 20183 |
| S | 62,076.00 | 2018 | 3 | 20183 |
| T | 21,019.00 | 2018 | 3 | 20183 |
Event Table:
Event Table
| Event | Metric Component | Date/Time | Year-Mon |
| 1 | 10 | 1/1/2018 5:30AM | 2018-1 |
| 2 | 42 | 1/3/2018 5:30AM | 2018-1 |
| 3 | 5431 | 2/1/2018 5:30AM | 2018-2 |
| 4 | 249 | 2/1/2018 6:30AM | 2018-2 |
My Average Customer formula is:
Average Customers Served = AVERAGEX(
summarize('Customers by Center',
'Customers by Center'[Year],
'Customers by Center'[Month],
"CustomSum", SUM('Customers by Center'[Customers])
),
[CustomSum]
)The Metric (non cumulative version) is:
Metric = sum('Raw Data'[Component])/[Average Customers Served]And that works great, except that I'm wanting a graph that will show the month to month accumulation by year, and then I want to be able to drill into that line graph to see the day to day accumulation by month. I just can't get the Cumulative component to split based on year. The SelectedValue, do you think it's returning more than one element? Is that why it isn't returning anything at all when I specify <= or = year in the formula you provided? I think that methodology should work, but I'm not very familiar with how SelectedValue() works