Date & Value when an order is x days overdue

[Anonymous]

Hi PowerBi Community,

Having some difficulties with this question thats been put across me. 

I've been asked to provide both the date and Balance where the customer is late making a payment, >5 & >10 days overdue. Back story is that we are looking to potentially introduce penalty fees if a customer is late paying for their order, so trying to gague the best time frame to implement it without annoying customers too much. 

In the example below the customer the customer is 6 days overdue on the 13th Jan 2021 with an overdue balance of £30, and is 11days overdue on the 18th Jan 2021 with an overdue balance of £30. In scenarios where the data is incomplete and days 6/10 are missing it would be amazing if the date could be the next date eg 19th Jan 2021. 

Sample Power BI 

Sample Excel File

[v-alq-msft]

Hi, @Anonymous 

I assume that you want to summarize by customer, orderid and calculate the balance of the max date in each group. You may try the following measure. The pbix file is attached in the end.

SumBalance = 
SUMX(
    SUMMARIZE(
        'Sample',
        'Sample'[Customer],
        'Sample'[OrderID],
        "Re",
        var d = MAX('Sample'[Date])
        return
        CALCULATE(
            SUM('Sample'[Balance]),
            FILTER(
                'Sample',
                [Date]=d
            )
        )
    ),
    [Re]
)

Result:

If i misunderstand your thoughts, please show us the expected result with OneDrive for business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

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