NOTE: I have something very very similar here > Accrued Running Total - Summation Calculation. The difference between this last ask and this new ask is that at the beginning of the new year 2018 it will disregard any hours saved in 2017. What this question is asking is how do I capture the total monthly that we left off within 2017 as part of this new number (so taking XYZ + the new stuff added in 2018).
I am struggling to figure out this problem and not sure how to even classify it, but here it goes. What I've been asked is to do an accrued total from month to month. So any total from a previous month carries on to the next month indefinitely. Any subsequent month that a value is added will have the same properties.
Within my workbook, the data would technically be something like this:
The biggest challenge I'm trying to tackle is how to have the accrued value in 2018, still consider the continuing amounts in the previous year (2017 values carry over but we don't want to add all the prior months, just start with 2017 values and new 2018 values). Another requirement is that I'll have to break it down to partial months. So what I began doing is taking the hours down to a daily amount and using a datediff to develop a constant (the number of days the value has been in production).
The purpose of this is to capture total saved hours with automation. The automation tool is put into production, but every month the hours saved continue on to infinity. We are trying to capture the total hours of automation hours saved. My idea was trying to develop a formula to get a factor (in the example above is the number of whole months, but I think days may work better) and then multiply the month by the factor and add it to all other months in that year. Any ideas on how to accomplish this would be greatly appreciated.
TabQuestion-AccruedCalc.twbx 41.3 KB