Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |