Forum Discussion

Anonymous

Not applicable

7 years ago

Need help

Hello All, I need to find something from my dataset which seems to be complicated for me, if anyone could help. As you can see in the data, once a tenant moves out the net rent might...

Need Help

Show and Tell

Tips and Tricks

Anonymous

Not applicable

7 years ago

Hi Ashish_Mathur

Here is the link you can have the PB file.

Result which I am expecting.

Ashish_Mathur

Super User

7 years ago

Hi,

Try this calculated column formula

=if(LOOKUPVALUE(price_check[Net Rent/ sqm],price_check[einzug],CALCULATE(MAX(price_check[einzug]),FILTER(price_check,price_check[Apartment Index]=EARLIER(price_check[Apartment Index])&&price_check[einzug]<EARLIER(price_check[einzug]))),price_check[Apartment Index],price_check[Apartment Index])>[Net Rent/ sqm],"Lower",if(LOOKUPVALUE(price_check[Net Rent/ sqm],price_check[einzug],CALCULATE(MAX(price_check[einzug]),FILTER(price_check,price_check[Apartment Index]=EARLIER(price_check[Apartment Index])&&price_check[einzug]<EARLIER(price_check[einzug]))),price_check[Apartment Index],price_check[Apartment Index])<[Net Rent/ sqm],"higher","Same"))

Hope this helps.

Anonymous
Not applicable
7 years ago
Hi Ashish_Mathur, Thank you for the formula. But when I tried this, I'm getting "A table of multiple values was supplied where a single value was expected" error. What could be the reaosn for this ?
- Ashish_Mathur
  Super User
  7 years ago
  Hi,
  
  I did not face this problem in the file that you shared with me. Share the link from where i can download your PBI file.
  - Anonymous
    Not applicable
    7 years ago
    Hi Ashish_Mathur, Sorry because of company policy I cannot share whole file with you. But i will try to find why I am getting error in my file where data is nearly same as of the shared file.