Forum Discussion

vytas's avatar
vytas
Helper I
6 years ago
Solved

Debt Aging

Hello 

 

 I have table  

 and want to write a measure Debt Age in  Days which is how much sales days fit in Accounts Recievable. 

For example - 2015-01-09 accounts recievable is 109 and it is 9 days of sales. 
Measure should be - take accounts recievable  at any day and  go backwards on sale untill  sales sum reach debt sum then count how much it was sale days. 

 

Thank You  

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, vytas 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

    Debt Age in  Days = 
var _accountsrecievable = SELECTEDVALUE('Table'[AccountsRecievable])
var _date = SELECTEDVALUE('Table'[Date])
return
IF(
    _accountsrecievable = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date]<=_date
        )
    ),
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date]<=_date
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, vytas 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

    Debt Age in  Days = 
var _accountsrecievable = SELECTEDVALUE('Table'[AccountsRecievable])
var _date = SELECTEDVALUE('Table'[Date])
return
IF(
    _accountsrecievable = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date]<=_date
        )
    ),
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date]<=_date
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi vytas ,

     

    Does it work if you use the DAY function on your date sales ?

     

    Did I answer your question? Mark my post as a solution!
    Ricardo

    • vytas's avatar
      vytas
      Helper I
      6 years ago

      No, it's just match in example.  Measure should be - take accounts recievable sum at any day and  go backwards on sale untill  sales sum reach debt sum then count how much it was sale days.