AR Aging Buckets (Dynamic Based on User Selected CutOff Date)

Thanks v-yulgu-msft 

Let's see if I can better communicate this:

1. User selects the desired EOM date from the slicer - this is the cut-off date from which the report is being run.
2. Desired goal is to show the AR Aging balance from the account's creation date up to the selected EOM date
3. All charges applied to the AR Account with a transaction_date on or prior to the selected EOM should be summed
4. We then age the charges based how many days have passed from the transaction's due_date to the selected EOM date
5. Similarly, all payments applied to the AR Account with a transaction_date prior to or on the selected EOM date should be summed
6. We then need to apply the summed payments made in the selection scope to the aged charges, only we need to apply payments to the oldest charges first (i.e. start at 91+ bucket) and if there is any remainder, it will then be applied to the next oldest charges that are available (i.e. 61-90 bucket, then 31-60 bucket and so on).

From what I can tell looking at other posts for aged debtors, their models normally have a "cleared_date" of some sort associated with the transaction charges. So they have trans_date, or when the charge actually took place, and then a clear_date, or when the charge was indicated as being paid. If I had a cleared_date in my data that would make this exercise trivial, unfortunately all I have are transaction dates - there is no separate field that contains the date when the charge has officially been considered paid. It all has to be calculated on the fly.

I've linked to a newer version of the PBIX which has some small changes to hopefully make my needs clearer. It includes some cards to show all the charges that were made prior to the cut off date as well as all the payments made prior to the cut off date. I'm using 5/31/2013 for my example here.

As you can see the $1,964.29 for the charges card in the picture above is simply all the charges shown in the table below added ($337.26 due 3/31/2013 + $928.96 due 4/30/2013 + $356.84 due 5/31/2013 = $1,623.06 + $341.23 due 6/30/2013 = $1,964.29)

Additionally, the payments shown in green above add correctly up to show that this account paid their balances due by 5/31/2013 in full. (Remember the $341.23 charged to the account between 5/17/2013 adn 5/31/2013 are not due until the following month 6/30/2013).

And here is a really bad mock-up of what I was hoping I could achieve. I'd basically want another row underneath the charges row for the account id that shows the payments being applied. In this case, since this account has paid all charges on time they all zero out - but not all accounts will be like this; there will be times where underpayments and/or over-payments are made. I've broken it down in to three additional rows only to show how were taking the total available payments for the period (in red text) and applying them to the buckets in reverse order of age (oldest first).

One last mock up of my desired end visual - this is just typed up in Excel not based on any real data, but should give a good idea of what I'm aiming for: