Hello,
I am trying to create a measure that counts the number of months it takes to reach an ending accounts receivable balance. As an example, I start with a measure that calculates an ending AR balance which is a calculation of values from different columns and tables and must look at every AR transaction regardless of date (that measure is already available.) What I now need to do is figure out how many months, counting backwards and including any fractional amount, starting with the most recent month (either selected or today's date) it takes to accumulate fees that add up to the ending ar balance. As an example, assuming we're looking at an AR balance at the end of 2016 $1,000:
Ending December AR Balance $1,000
December Fees: $200
November Fees: $300
October Fees: $400
September: $300
Using these values, the Months outstanding would be 3.33 (200 + 300 + 400 + (100/300).)
Part of me is hoping this is a complex and difficult measure so as to soften the expected ego blow; I can't even seem to conceptualize a possible solution.
Any suggestions would be greatly appreciated.
Thanks,
Eric
