Creating a calculated column that depends on two different conditions

[stevehuff]

Hello! New to PowerBI and hoping that I can get some assistance in building out a report. We're looking to generate a report to show the percentage increase/decrease for lease amounts tied to the same building. The report would need to look to see 1) If the building is the same and 2) Calculate the percentage increase or decrease depending on if there is a prior move-in date for the building. If there is no prior move-in date or it is the sole record for the building, then the calculation would not run and the value would be left blank.

Based on how I have currently sourced/sorted the data, it looks something along the lines of this: 

Building	Move-In Date	Rent
Building 1	10/20/2022	$1,500
Building 1	08/04/2023	$1,650
Building 2	05/10/2023	$1,700
Building 3	04/02/2021	$1,650
Building 3	08/10/2023	$1,600
Building 4	01/01/2022	$1,800

From what I have been reading, it seems like a calculation would be the best method on how to generate the increases/decreases, but I'm a little lost on how to write the formula to calculate the percent increases only when the building name is the same and there is a previous move-in date value. Once complete, we'd want the report to look something along the lines of this:

Building	Move-In Date	Rent	Percent Increase/Decrease
Building 1	10/20/2022	$1,500
Building 1	08/04/2023	$1,650	+10%
Building 2	05/10/2023	$1,700
Building 3	04/02/2021	$1,650
Building 3	08/10/2023	$1,600	-3%
Builidng 4	01/01/2022	$1,800

Any and all help would be appreciated. Thanks in advance! 😁

[stevehuff]

That worked @ryan_mayu. Thank you so much! Thanks for your suggestion as well @Ashish_Mathur! 

[ryan_mayu]

you are welcome

[Ashish_Mathur]

Hi,

Write these calculated column formulas

Previous rent = LOOKUPVALUE(Data[Rent],Data[Move-In Date],CALCULATE(MAX(Data[Move-In Date]),FILTER(Data,Data[Building]=EARLIER(Data[Building])&&Data[Move-In Date]<EARLIER(Data[Move-In Date]))),Data[Building],Data[Building])

Rent growth (%) = DIVIDE(([Rent]-[Previous rent]),[Previous rent])

Hope this helps.

[ryan_mayu]

@stevehuff 

you can try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[Building]=EARLIER('Table'[Building])&&'Table'[Move-In Date]<EARLIER('Table'[Move-In Date])),'Table'[Move-In Date])
return if(ISBLANK(_last),BLANK(),'Table'[Rent]/maxx(FILTER('Table','Table'[Building]=EARLIER('Table'[Building])&&'Table'[Move-In Date]=_last),'Table'[Rent])-1)

[stevehuff]

Hey @ryan_mayu 

Thanks for the help! I'm still having some difficulties here. When I entered this in, I got the following error message. Any ideas on how/where to convert the Value/Format function?

[ryan_mayu · Accepted solution]

@stevehuff 

is your data format correct? pls make sure the move in date is date type.