Help with calculating change/difference over period

Question

hoping a simple solution but i'm trying to calculate the change in Advance Rate and Borrowing Base from one period to the next for a portfolio of loans. there are hundreds of loans in the portfolio and my dataset includes this portfolio of loans for each date below and the dates are not necessarily month ends. should i be using a calculated column or better to use a measure? ideally, id like to be able to toggle the date range i'm calculating hte change over...i.e. 1/31/2020&nbsp; - 4/17/2020 or 3/31/2020 - 4/17/2020DateCompanyInvestment TypeLoan BalanceAdvance RateBorrowing Base1/31/2020Company 1Second Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,474,794100%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,474,7942/29/2020Company 1Second Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,474,79490%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,227,3143/31/2020Company 1Second Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,474,79485%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,103,5754/17/2020Company 1Second Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,474,79480%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,979,8351/31/2020Company 2First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,258,21195%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,095,3012/29/2020Company 2First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,249,94195%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,087,4443/31/2020Company 2First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,249,94195%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,087,4444/17/2020Company 2First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,249,94195%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,087,4441/31/2020Company 3First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 549,911100%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 549,9112/29/2020Company 3First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 548,49790%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 493,6483/31/2020Company 3First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 548,49780%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 438,7984/17/2020Company 3First Lien Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 548,49770%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 383,948

v-xicai · Accepted Answer

Hi Jkaplan88&nbsp;,
&nbsp;
&gt;&gt;hoping a simple solution but i'm trying to calculate the change in Advance Rate and Borrowing Base from one period to the next for a portfolio of loans.&nbsp;
&nbsp;
From your description, the words "from one period to the next for a portfolio of loans" means you need to compare the "Second Lien Loan" with the "First Lien Load" of&nbsp; Investment Type, right? If so, you may refer to the similar case: Calculate variance from previous date.
&nbsp;
Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive&nbsp;and post&nbsp;the link here. Do mask sensitive data before uploading.
&nbsp;
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
&nbsp;
Best Regards,
Amy
&nbsp;
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Help with calculating change/difference over period

2 Replies

Related content

Dynamic Calculation Different Period

Calculate difference and percentage change between two columns dynamically

Calculate period over period

Calculate % change between series of different date

Previous Period Difference and Percent Change

Date	Company	Investment Type	Loan Balance	Advance Rate	Borrowing Base
1/31/2020	Company 1	Second Lien Loan	2,474,794	100%	2,474,794
2/29/2020	Company 1	Second Lien Loan	2,474,794	90%	2,227,314
3/31/2020	Company 1	Second Lien Loan	2,474,794	85%	2,103,575
4/17/2020	Company 1	Second Lien Loan	2,474,794	80%	1,979,835
1/31/2020	Company 2	First Lien Loan	3,258,211	95%	3,095,301
2/29/2020	Company 2	First Lien Loan	3,249,941	95%	3,087,444
3/31/2020	Company 2	First Lien Loan	3,249,941	95%	3,087,444
4/17/2020	Company 2	First Lien Loan	3,249,941	95%	3,087,444
1/31/2020	Company 3	First Lien Loan	549,911	100%	549,911
2/29/2020	Company 3	First Lien Loan	548,497	90%	493,648
3/31/2020	Company 3	First Lien Loan	548,497	80%	438,798
4/17/2020	Company 3	First Lien Loan	548,497	70%	383,948