How best to calculate the reducing income over time

[hayesla]

Hi

I'm fairly new to Power Bi and can't work out how to calculate and visualize reducing income over time.

The use case is that I have a tenancy schedule with a list of tenants, their annual rent, lease start date, and lease end date. I want to show how my income will fall over time as the lease's end. The starting point would be total rent of all the tenants as at the date of the tenancy schedule and I want to show this total reducing as the leases reach their end date.

Thanks

Laurence

[v-robertq-msft · Accepted solution]

Hi, @hayesla 

According to your description, I can understand the output you want to achieve, you want to get a chart to show your sum of rent based on Date, you can follow my steps:

This is my test data based on your description:

1. Create a Calendar table to put in the line chart:

Date = CALENDAR(TODAY(),DATE(2022,12,31))

2. Create a measure:

Sum of rent =

CALCULATE(SUM('Table'[Rent]),FILTER(ALLSELECTED('Table'),[End Date]>=MAXX(RELATEDTABLE('Date'),[Date])))

3. Create a line chart and place column and measure like this, and you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@hayesla , Is some financial function like PV can help ? If so refer

https://www.youtube.com/watch?v=9VYqbj2h4zE

https://www.youtube.com/watch?v=6oaSdjZ_FLQ

[hayesla]

Thanks for the suggestion but this isn't what I am after. I essentially want to start with the total of all the rent and then deduct the value of each tenant's rent as the end date of the lease occurs. For example:

Today I have 10 tenants each paying £1000 rent pa = £10,000

Tenant 1 has a lease end date next week so my total rent next week  is £9,000

Tenant 2 has a lease ending in 3 weeks, my total rent will then be £8,000

I would like to plot a graph showing the rent going down from £10,000 to £8,000