Answered

How to use years(between start date and end date) as filter and show new rate for that selected year?

(0) Share

Report

Posted on by r_voyager

130

So I have sharepoint list connected as data source with my powerapps. I have some columns in my edit form such as kit fees, agreement start date , agreement validity, agreement renewal date, rate of increase and new rate year wise.

So the thing I want is new rate year wise.

Let's say kit fees is 10000 ,agreement start date is 21/03/2022,agreement validity is 5 years,agreement renewal date will be= agreement start date(21/03/2022) + agreement validity(5 years) i.e 21/03/2027, rate of increase is 5% , new rate year wise will be fee(10000) + 5% of fee(10000) = 10500 for 2023 year.

Now I want to calculate new rate year wise for every year after agreement start date. For eg for 2023 it was 10500 and for 2024 it will be 10750 = (10500+5% of (10500).

I can calculate new rate year wise only for next year. I want to calculate it for every year following agreement start date based on increase rate input.

How to show it? How to use years(between start date and renewal date) as filter and show new rate for that selected year?

Stucked in it for 2 days now.

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

rubin_boer 4,843 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
hi @r_voyager I will try to explain a solution using variables instead of controls, you can do that ;).

Consider the following:

Calculating the annual fees based on rate
Variables
Set(varStart,DateValue("21/03/2022")); Set(varValPeriod,5);
Set(varRenewal,DateAdd(varStart,varValPeriod,Years));
Set(varRate,0.05); Set(varStartingFee,10000);
Calculating the annual fees
Adding 5% annual this is the results expected - this differs from your 2024 which is 10750 (have a look at that)
initial 10000 10500 11025 11576.25 12155.0625

Formula to calculate interest

ForAll( Sequence(varValPeriod), Collect( colFees, With( { _fee: If( IsBlank( Max( colFees, fee ) ), varStartingFee, Max( colFees, fee ) ) }, { year: Year(varStart) + Value, fee: _fee + _fee * varRate } ) ) )

Add a combobox and a label.

Combobox to list the years

label Text = Lookup(colFees, year = ComboBox.Selected.Value).fee

Result

Hope it helps,

R
r_voyager 130 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @rubin_boer , thank you so much for the solution. exactly what I was looking for. And sorry for my miscalculation in the question posted. Btw I used dropdown instead of comboBox , I don't know why but comboBox and label combination was not working for me.

Thanks,
Rahul