Help with calculating change/difference over period

[Jkaplan88]

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  - 4/17/2020 or 3/31/2020 - 4/17/2020

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

[v-xicai · Accepted solution]

Hi @Jkaplan88 ,

>>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. 

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  Investment Type, right? If so, you may refer to the similar case: Calculate variance from previous date.

Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

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.

[Greg_Deckler]

Column or measure, it depends. But, in general, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....

I realize it sounds like it has nothing to do with your situation, but it does.