Debt Aging

[vytas]

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  

[v-alq-msft · Accepted solution]

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.

[amitchandak]

@vytas , What is formula for this

[camargos88]

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]

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.