Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have an Accounts Receivable aging report that I want to update as we receive funds. The amounts received are not consistent with the exact dollar amount of the dues, so I have to subtract the amounts received from the earliest first.
For example, from the report below, if we receive $10 from Bank of America Amex, it should be subtracted from the earliest (30 days) first and then the remaining from the recent (Current due).
So after updating, there should be nothing under 30 days due. The current due would be $7.64.
I couldn't figure out a way to start working on this. Any help would be greatly appreciated, thank you!
This is how the aging report looks like:
This is how the data looks:
The data of amounts received looks like this:
The relationship between the two table is many to many:
Hi @svelamuri
try below measure
Current Due =
VAR RemainingDues =
ADDCOLUMNS (
FILTER (
AgingReport,
[Amount Due] > [Amount Received]
),
"RemainingDues", [Amount Due] - [Amount Received]
)
RETURN
SUMX ( RemainingDues, [RemainingDues] )
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi @johnbasha33
Thank you for your response.
However, this doesn't work in my case because Amount Due and Amount received are not in the same table or the same format. Please refer to the screenshots - let me know if you need more information.
Also, I need to be subtracting the amount received from amount due in the descending order of the aging category - so subtract from 60 days first (if anything in 60 days exists), then any remaining amount received from 30 days and lastly from current due.
Thank you!