Forum Discussion
Another Running Total Question
OK I've got a running total measure that works fairly well sort of.
Metric Cumulative = calculate(
sum('Raw Data'[Component])/[Average Customers],
Filter(
Allselected('Raw Data'),
'Raw Data'[Start Date]<=max('Raw Data'[Start Date])
)
)However, when I put this on a line graph, with Year in the legend and Start Date on the axis (but no year or quarter option, straight down to the months), it plots it somewhat strangely. The 2013 data starts at zero and accumulates just fine. But the 2014 data starts in January at the same point that the 2013 data ended at. So the accumulation doesn't start over for each category of the legend. What am I doing wrong?
Here's what it looks like currently:
And here's what I want it to look like:
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.
7 Replies
Anonymous
Give it a shot
Metric Cumulative = CALCULATE ( SUM ( 'Raw Data'[Component] ) / [Average Customers], FILTER ( ALLSELECTED ( 'Raw Data' ), 'Raw Data'[Start Date] <= MAX ( 'Raw Data'[Start Date] ) && YEAR ( 'Raw Data'[Start Date] ) = YEAR ( SELECTEDVALUE ( 'Raw Data'[Start Date] ) ) ) )Hm that didn't work, nothing is plotting at all. I tried adjusting the legend / axis settings but no dice. It's acting as if there are no records where that matches.
I tried setting it to <= and still nothing plotted, but when I set it to >= it plotted the same graph as before. It would need to be = but I don't know why it doesn't work.
Anonymous
Could you share your file?
Anonymous,
Based on your first screenshot and your sample data, the Metric Cumulative value for January of 2014, 2015, 2016,2017 and 2018 years don't equal to 0, do you want to set the Metric Cumulative value to be 0? If so, could you please share more data of "Raw data" table for us to test?
Regards,
LydiaRight, January wouldn't be equal to 0, but Jan 1st of the year at midnight would be 0.
Basically my data is very granular, and I have rows of data for almost every hour of every day. I would need it to reset to zero exactly at midnight, but even by 1am on new year's day I'll probably have data. Below is a moderately anonymized version of the table. Dates and stuff are the same. I have calculated columns that don't show here for Year(start date), month(start date), day(Start date). Also have Year&Month (ie 201804), and year month territory (ie 201804A). The customer table also has year month territory column (ie 201804A) which is what the relationship is based on.
ID Start Date End Date Status Metric Component 2 Length Metric Component Territory C# UODID Cause View Source 15598583 4/1/13 0:00 4/1/13 14:46 CMP 0 886.4166667 0 A 839.2634767 350.295675 Outside Factor PM 1.7 15598584 4/1/13 0:00 4/1/13 18:23 CMP 0 1103.383333 0 B 3177.056381 39.37354854 Planned Request Customer PM 1.7 15598585 4/1/13 0:21 4/1/13 1:10 CMP 2 49 98 C 36660.45295 3067.457455 Failure Wear PM 1.7 15598586 4/1/13 1:51 4/1/13 5:06 CMP 0 195.3166667 0 D 2694.555091 2148.102147 Outside Factor Fire PM 1.7 15598587 4/1/13 2:24 4/1/13 4:45 CMP 8 140.4666667 1123.73333 D 6912.50926 5185.59996 Outside Factor Fire PM 1.7 15598588 4/1/13 4:21 4/1/13 5:50 CMP 2 88.26666667 176.533333 E 5885.557965 4763.835065 Animal PM 1.7 15598589 4/1/13 6:19 4/1/13 6:53 CMP 1 33.86666667 33.8666667 C 39680.6477 36486.94172 Unselected PM 1.7 15598590 4/1/13 7:18 4/1/13 9:15 CMP 1 116.6833333 116.683333 F 4205.015999 1036.788266 Animal PM 1.7 15598591 4/1/13 7:20 4/1/13 7:36 CNL 1 15.85 15.85 Warrensburg 6204.30426 1094.586421 Unknown PM 1.7 15598592 4/1/13 7:21 4/1/13 9:01 CMP 0 99.7 0 C 2313.85771 1892.462309 Unselected PM 1.7 15598593 4/1/13 7:22 4/1/13 8:36 CMP 0 74 0 C 2284.255074 1519.403311 Unselected PM 1.7 15598594 4/1/13 7:37 4/12/13 18:00 CNL 0 16463.08333 0 A 313.0291175 156.1489578 Unknown PM 1.7 15598595 4/1/13 7:54 4/1/13 13:53 CMP 0 358.2833333 0 G 18594.87236 6179.357804 PM 1.7 15598596 4/1/13 8:08 4/1/13 11:01 CMP 1 172.6166667 172.616667 H 19719.91549 18605.85861 PM 1.7 15598597 4/1/13 8:10 4/1/13 12:53 CMP 0 282.65 0 A 44.9209252 29.55197266 Unselected PM 1.7 15598598 4/1/13 8:13 4/2/13 12:45 CMP 0 1711.6 0 A 2765.036062 1001.251323 Failure Wear PM 1.7 15598599 4/1/13 8:14 4/1/13 17:40 CMP 2 565.3 1130.6 I 2338.265835 834.2864042 Planned Request PM 1.7 15598600 4/1/13 8:15 4/1/13 11:54 CMP 0 219 0 C 25071.04088 16298.84957 Unselected PM 1.7 15598601 4/1/13 8:17 4/1/13 17:03 CMP 1 525.7666667 525.766667 J 2118.356997 414.0393082 Planned Request PM 1.7 15598602 4/1/13 8:21 4/1/13 9:16 CMP 0 54.1 0 K 25165.46525 210.0593581 Vegetation Trees PM 1.7 15598603 4/1/13 8:21 4/3/13 12:59 CMP 0 3157.6 0 L 7841.892158 1030.431465 Unselected PM 1.7 15598604 4/1/13 8:30 4/1/13 11:13 CMP 1 162.2 162.2 G 22797.22658 10372.7451 Failure Wear PM 1.7 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.
