Dynamic Receivables aging

[bzjens]

Hi,

I have spent a few days trying to find a solution here, and I cannot find a post in here that sets me in the right direction.

I have a list of invoices from our accounting system with the following columns and a date slicer:

(Invoice table and date table not related)

The above image looks correct, and I have been using this formula for the "amountRemaining" measure:

amountRemaining = IF(MIN(Invoices[dueDate]) < MIN('Calendar'[Date]) &&
    MAX('Calendar'[Date]) > MIN(Invoices[datePaid]), 
    SUM(Invoices[invoiceAmount]) - SUM(Invoices[amountPaid]), SUM(Invoices[invoiceAmount]))

All good until I choose a date after one of the datePaid dates:

amountRemaining looks OK for the client that has made the payment (Client B), but the total is now wrong

It shows 300 000 - but it should be 530 000.

I need this total for a bar chart with date as x-axis.

Anyone knows how to fix this?

Thanks,

Børre

[TheoC]

Hi @bzjens 

Can you recreate the measure in the form of a Calculated Column then add that to the table?  This may lead to a quicker resolution. 

Otherwise, you will likely need to create a secondary measure using the "IF ( HASONEFILTER (Measure) = TRUE() , Measure1 , Measure2)" philosophy as explained by Greg in this post: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Hope the above is of some type of help 🙂

Theo

[bzjens]

Hi @TheoC 

Thanks for replying!

I think I will have trouble using calculated columns as I'm aggregating over rows based on dates in the slicer

(To have my chart showing i.e. month by month)

But Greg's post is likely the way to go.

Thanks for sending me in that direction!

Børre

[TheoC]

@bzjens absolutely mate. No trouble at all.

If it's not exactly what you were after, you may be able to achieve the outcome you're wanting by adjusting your SUMs to SUMXs. For example:

amountRemaining = IF(MIN(Invoices[dueDate]) < MIN('Calendar'[Date]) &&
    MAX('Calendar'[Date]) > MIN(Invoices[datePaid]), 
    SUMX('Invoices' , Invoices[invoiceAmount]) - SUMX('Invoices' , Invoices[amountPaid]), SUMX('Invoices',Invoices[invoiceAmount]))

Let me know either way!

Theo 🙂