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.
I have the below table. I need to calculate the outstanding amount as per the below formula.
Outstanding Amount = (Principal Amount - Principal Amount * Percentage)
So for the first date range (31/12/2020 - 29/6/2021), the outstanding amount will be (100 - (100*0.0136)) = 98.64
for the next date range (30/6/2021 - 30/12/2021), the outstanding amount will be (98.64 - (100*0.0197) = 96.67
As you can see, I need to take the previous value to calculate the outstanding for each date range. How do I accomplish this for each project?
Tranche | Repayment Schedule Start Date | Repayment Schedule End Date | % | Percentage | Project Name | Principal Amount (USD) |
A | 31/12/2020 | 29/6/2021 | 1.36 | 0.0136 | Project A | 100 |
A | 30/6/2021 | 30/12/2021 | 1.97 | 0.0197 | Project A | 100 |
A | 31/12/2021 | 29/6/2022 | 4.05 | 0.0405 | Project A | 100 |
A | 30/6/2022 | 30/12/2022 | 4.02 | 0.0402 | Project A | 100 |
A | 31/12/2022 | 29/6/2023 | 4.2 | 0.042 | Project A | 100 |
A | 30/6/2023 | 30/12/2023 | 4.17 | 0.0417 | Project A | 100 |
A | 31/12/2023 | 29/6/2024 | 4.36 | 0.0436 | Project A | 100 |
A | 30/6/2024 | 30/12/2024 | 4.36 | 0.0436 | Project A | 100 |
A | 31/12/2024 | 2/6/2025 | 4.53 | 0.0453 | Project A | 100 |
A | 3/6/2025 | 3/6/2025 | 66.98 | 0.6698 | Project A | 100 |
Solved! Go to Solution.
Hi @Kolumam2810 ,
Try this:
Column:
Column =
'Table'[Principal Amount (USD)]
- SUMX (
FILTER (
'Table',
'Table'[Tranche] = EARLIER ( 'Table'[Tranche] )
&& 'Table'[Repayment Schedule Start Date]
<= EARLIER ( 'Table'[Repayment Schedule Start Date] )
),
'Table'[Principal Amount (USD)] * 'Table'[Percentage]
)
Measures:
Measure =
SUM ( 'Table'[Principal Amount (USD)] )
- SUMX (
FILTER (
ALLSELECTED('Table'),
'Table'[Tranche] = MAX ( 'Table'[Tranche] )
&& 'Table'[Repayment Schedule Start Date]
<= MAX( 'Table'[Repayment Schedule Start Date] )
),
'Table'[Principal Amount (USD)] * 'Table'[Percentage]
)
Measure - ModifiedTotalValue = SUMX('Table',[Measure])
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kolumam2810 ,
Try this:
Column:
Column =
'Table'[Principal Amount (USD)]
- SUMX (
FILTER (
'Table',
'Table'[Tranche] = EARLIER ( 'Table'[Tranche] )
&& 'Table'[Repayment Schedule Start Date]
<= EARLIER ( 'Table'[Repayment Schedule Start Date] )
),
'Table'[Principal Amount (USD)] * 'Table'[Percentage]
)
Measures:
Measure =
SUM ( 'Table'[Principal Amount (USD)] )
- SUMX (
FILTER (
ALLSELECTED('Table'),
'Table'[Tranche] = MAX ( 'Table'[Tranche] )
&& 'Table'[Repayment Schedule Start Date]
<= MAX( 'Table'[Repayment Schedule Start Date] )
),
'Table'[Principal Amount (USD)] * 'Table'[Percentage]
)
Measure - ModifiedTotalValue = SUMX('Table',[Measure])
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |