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 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

 

12 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    As per my knowledge, your requirement cannot be achieved directly.

     

    My thread is to first filter table based on the slicer selection, like

    Table = FILTER(ALL('Months Outstanding'),'Months Outstanding'[Month]<=MAX('Months Outstanding'[Month]))

     

    Then, add a column to calculate running total, sum the all the fees before the selected Month.

    Table=ADDCOLUMNS(TableName,"runningTotal",CALCULATE(SUM(TableName[Fees]), TableName[Month]>=EARLIER(TableName[Month])))

     

    In that case, we can compare the running total value with Ending AR Balance, we can count the Month whose running total value is small than AR Balance. However, based on my research, it's hard to get the next Month name and its corresponding fees. 

     

    I didn't find any valid solution to achieve that. If I have any idea, I will come back to update this question.

     

    Thanks,
    Yuliana Gu

    • 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
        Community 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.