Hi Friends,
Let me explain my issue:
Each Invoice has many part payments and a seperate row appears for it. The closedate is the date payment came.
Running total Column - For each invoice - the order in which payment came.
Balance Amount Column - Balance amount as of Close date.
I created another table with only Start of month dates in it MonthStartDate[MonthStartDate] with 1-> many relationship on CloseDate.
01-04-2019
01-05-2019
01-06-2019
Problem statement -> Show outstanding (Total Balance amount) at 1st of each month
Approach ->
Step 1 -> Select all data before that 1st of month. The below measure gives all the rows for all the invoices and all payments.
CummulativeSUM 1 = CALCULATE( SUM('Outstanding Report'[BalanceAmount]), FILTER(
ALLSELECTED('Outstanding Report'),
'Outstanding Report'[CloseDate] < MAX(MonthStartDate[MonthStartDate])
) )
Step 2 - In the dataset of Step1, find the latest payment record for each Invoice. Means -> Pick the MAX (Running total) for each Invocie. Below Measure gives me max record in the WHOLE Data set
MaxRecord =
CALCULATE( MAX('Outstanding Report'[Running Total]), ALLEXCEPT('Outstanding Report', 'Outstanding Report'[Invoice]) )
PROBLEM : HOW do I combine Step1 & Step2.
Sample Dataset:
| Invoice | Invdat | CloseDate | AmountReceived | BalanceAmount | Running Total |
| S52-11900010 | 20-04-2019 | 20-04-2019 | 0 | 12524 | 1 |
| S52-11900010 | 20-04-2019 | 20-06-2019 | -12524 | 0 | 2 |
| S52-11900011 | 20-04-2019 | 20-04-2019 | 0 | 57375 | 1 |
| S52-11900011 | 20-04-2019 | 22-05-2019 | -325 | 57050 | 2 |
| S52-11900011 | 20-04-2019 | 22-04-2019 | -55000 | 2050 | 3 |
| S52-11900011 | 20-04-2019 | 22-05-2019 | -2050 | 0 | 4 |
| S52-11900012 | 25-04-2019 | 25-04-2019 | 0 | 2251 | 1 |
| S52-11900012 | 25-04-2019 | 25-05-2019 | -2250 | 1 | 2 |
| S52-11900012 | 25-04-2019 | 25-06-2019 | -1 | 0 | 3 |
| S52-11900013 | 25-04-2019 | 25-04-2019 | 0 | 21735 | 1 |
| S52-11900013 | 25-04-2019 | 25-05-2019 | -21735 | 0 | 2 |
| S52-11900014 | 26-04-2019 | 26-04-2019 | 0 | 25674 | 1 |
| S52-11900014 | 26-04-2019 | 26-05-2019 | -25000 | 674 | 2 |
| S52-11900014 | 26-04-2019 | 27-06-2019 | -674 | 0 | 3 |
| S54-11814413 | 27-02-2019 | 27-02-2019 | 0 | 33651 | 1 |
| S54-11814413 | 27-02-2019 | 27-04-2019 | -33644 | 7 | 2 |
| S54-11814600 | 27-03-2019 | 27-03-2019 | 0 | 29576 | 1 |
| S54-11814600 | 27-03-2019 | 27-04-2019 | -29574 | 2 | 2 |
| S56-11810108 | 30-03-2019 | 30-03-2019 | 0 | 33901 | 1 |
| S56-11810108 | 30-03-2019 | 12-04-2019 | -33900 | 1 | 2 |
| S56-11900001 | 30-04-2019 | 30-04-2019 | 0 | 19499 | 1 |
