Find last Records that add up to calculated Value

[Raivo_S]

Hello,

I need a way to find last bills sent to a customer that add up to his total debt as well as the oldest bill he has not payed.

Example

If debt for Customer AAA is 500, I need to get list of last 2 bils: "003, 004" and date 06.01.2023.

If debt for a Customer AAA is 600, this list should also include secound bill, so result would be "002, 003, 004" and date 04.01.2023.

Would really appreciate a help with this

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

1. add customer and calendar tables

2. add relationships:

3. dax:

This measure will return the date required:

Earliest date =

var _debt = MAX('Table (2)'[Debt])

var _Date = MAX( 'Table'[Date] )

 var _inverse_RT =

    CALCULATE(

        SUM('Table'[Value]),            

        'Calendar'[Date]  >= _Date,  

        ALL('calendar'), ALL('Table'[Date]),ALL('Table'[Id])            

    )

    var _remaining_debt = _debt-_inverse_RT

 return

CALCULATE(MAX('Table'[Date]),FILTER('Table',_remaining_debt<=0),ALL('Table'[Date]))

This measure is used as a filter to show only the rows including the correct ids:

Filter for ids =

var _debt = MAX('Table (2)'[Debt])

var _Date = MAX( 'Table'[Date] )

 var _inverse_RT =

    CALCULATE(

        SUM('Table'[Value]),            

        'Calendar'[Date]  >= _Date,  

        ALL('calendar'), ALL('Table'[Date]),ALL('Table'[Id])            

    )

    var _remaining_debt = _debt-_inverse_RT

 return

IF(OR(_remaining_debt>=0, _remaining_debt+MAX('Table'[Value])>0),1,0)

Place the filter measure like this:
tests:

Changing values of debt 500 -> 600 and 300 -> 250

This should return 3 rows for AAA and 1 for BBB:
The test works as expected.


The logic here is to use inverse running total to compute the breakpoint when the debt becomes negative instead of positive. This date marks when the debt is paid. The filter measure returns rows which either have positive value or which are at this breakpoint.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Raivo_S]

Thank you for help!
My end result was getting date of the oldest unpayed bill and list of unpayed bills next to customer in my customer table. Had to modify measures to archive that, but idea stayed the same as you suggested.

I had measure that calculates sum of all the future bills including selected one

future_bill_sum =
VAR date_k =
MAX ( BILLS[DATE] )
RETURN
CALCULATE (
SUM ( BILLS[VALUE] ),
ALL ( BILLS[ID] ),
ALL ( BILLS[DATE] ),
FILTER ( ALL ( DIM_CALENDAR ), DIM_CALENDAR[DATE] >= date_k )
)

Then I had measure to find the oldest unpayed bill date

oldest_bill_date =
VAR debt =
MAX ( DIM_CUSTOMERS[Debt] )
RETURN
CALCULATE (
MAXX (
FILTER (
SUMMARIZE ( BILLS, BILLS[ID], BILLS[DATE], "current_sum", [future_bill_sum] ),
[current_sum] >= debt
),
BILLS[DATE]
)
)

And another mesure to get the list of unpayed bills

list_of_bills =
VAR date_from = [oldest_bill_date]
RETURN
CONCATENATEX (
FILTER ( SUMMARIZE ( BILLS, BILLS[ID], BILLS[DATE] ), BILLS[DATE] >= date_from ),
BILLS[ID],
", "
)

In the end I got this result: