Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Months Outstanding Measure

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...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Yuliana,

 

Thanks so much for the suggestion. I'm going to work with what you've provided and will report back regarding any failures or successes.

 

I really appreciate it!

 

Eric

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

You might consider to take a more standardized calculation for this task instead. What you try to do here is a month-version of the so called Days-Sales-Outstanding (DSO) calculation like described here:

 

http://www.investopedia.com/terms/d/dso.asp

 

This would mean that you take an x-months running average of the Fees and relate them to your current balance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ImkeF,

     

    Thanks for the suggestion, Imke. I'm finally at a point where I have a chance to work on this and will be able to mess around with the formula in the investopedia article.

     

    Thanks,

     

    Eric Theil

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Did you ever figure this out? I am doing the exact same thing. And the accountants don't want the "simplified" version of DSO suggested in investipedia. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Did you ever figure this out? I am doing the exact same thing. And the accountants don't want the "simplified" version of DSO suggested in investipedia. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I came up with a couple of solutions. I got some help from a consultant service that wrote something. It worked great, but I could never follow the logic of the formula.

         

        Below is a version I came up wth later. It's less flexible in that it only looks back six months or periods. You have to define Net M1 through Net M6 using the pattern below (Net M1 and Net M2 measures) along with a calculation to get your net receivables. The third measure, MO, then calculates six months back.

         

        Net Receivables:= 
VAR maxReservePeriod = [Latest Reserve Period]
RETURN
[Ending AR Balance] + [Ending WIP Balance] - [Net Reserves]
- CALCULATE(
	[Unapplied Amount],
	FILTER(
		ALL('Date'),
		'Date'[Reserve Period] = maxReservePeriod
		&& 'Date'[Period] <>  maxReservePeriod
	)
)

Net M1:= 
var endDate = LASTDATE('Date'[Date])
RETURN
CALCULATE(
	[Fee Amount] + [Expense Amount] - [Reserve Allowance],
	SUMMARIZE(
		FILTER(
			ALL('Date'[Date], 'Date'[Period]),
			'Date'[Date] <= endDate
			&& 'Date'[Date] >= endDate
		),
		'Date'[Period]
	)
)

Net M2:= 
var endDate =DATEADD(LASTDATE('Date'[Date]), -1, MONTH)
RETURN
CALCULATE(
	[Fee Amount] + [Expense Amount] - [Reserve Allowance],
	SUMMARIZE(
		FILTER(
			ALL('Date'[Date], 'Date'[Period]),
			'Date'[Date] <= endDate
			&& 'Date'[Date] >= endDate
		),
		'Date'[Period]
	)
)

         

         

         

        MO:= 
var netRec = [Net Receivables]
var m1 = [Net M1]
var m2 = [Net M2]
var m3 = [Net M3]
var m4 = [Net M4]
var m5 = [Net M5]
var m6 = [Net M6]
RETURN
IF(
	netRec = BLANK(),
	BLANK(),
	IF(
		m1 > netRec,
		DIVIDE(netRec, m1),
		IF(
			m1 + m2 > netRec,
			1 + DIVIDE(netRec - m1, m2),
			IF(
				m1 + m2 + m3 > netRec,
				2 + DIVIDE(netRec - m1 - m2, m3),
				IF(
					m1 + m2 + m3 + m4 > netRec,
					3 + DIVIDE(netRec - m1 - m2 - m3, m4),
					IF(
						m1 + m2 + m4 + m4 + m5 > netRec,
						4 + DIVIDE(netRec - m1 - m2 - m3 - m4, m5),
						IF(
							m1 + m2 + m4 + m4 + m5 + m6 > netRec,
							5 + DIVIDE(netRec - m1 - m2 - m3 - m4 - m5, m6),
							6
						)
					)
				)
			)
		)
	)
)