Forum Discussion
Creating a calculated column that depends on two different conditions
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! 😁
6 Replies
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)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.
That worked ryan_mayu. Thank you so much! Thanks for your suggestion as well Ashish_Mathur!
you are welcome
