Creating New Column in Data Set: Commercial Real Estate

[MikDumb]

Hello - I am creating a new report in PowerBi to try and capture the amount of time it takes my team to lease and build out a commerical space.  Eventually, I will want to filter this list into two buckets, if the space ever had a tenant in it or if the space was being re-tenanted.  

I am trying to write a new column that will determine if the space is being re-tenanted, and I think the best way to solve for this is to review the SpaceID (my unique identifier for each leasable space) and determine if that SpaceID shows up in another row in the data.  If it does show up in the data set multiple times, the first lease date would be the initial buildout, and every other occurance would be retenanting.  Below is a sample of my data.  The space ID 32142901 with a lease date of 8/28/2020 should show as a retenanting and 32142901 with a lease date of 3/15/18 should be intial buildout.

Thanks for the help!

[MikDumb]

The Buildout Type is the output column I would like to replicate in PowerBI.

[ryan_mayu · Accepted solution]

@MikDumb 

pls try this

Column = 
VAR _min=CALCULATE(min('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
VAR _max=CALCULATE(max('Table'[Leasing Date]),ALLEXCEPT('Table','Table'[SpaceID]))
return if('Table'[Leasing Date]=_min,"Initial Buildout",if('Table'[Leasing Date]=_max,"Retananting"))

[MikDumb]

That was it!  Thank you!!!!

[ryan_mayu]

you are welcome

[MikDumb]

Where I'm getting hung up is that the expected output is dependent upone other rows within the table.  Starting at the top of the sampled data set, the expected output is as follows:

• Space ID 32142501 - "Initial Buildout" - Logic: This is the only time this Space ID occurs in the table; therefore, it was the initial buildout of the space
• Space ID 32142901 (8/28/20 Lease Date) - "Retenanting" - Logic: This Space ID occurs multiple times; however, the lease date listed is not the earliest Lease ID for this Space ID.  Therefore, this was a retenanting of the space
• Space ID 32142901 (3/15/18 Lease Date) - "Initial Buildout" - Logic: This Space ID occurs multiple times; however, the lease date listed is the earliest Lease ID for this Space ID.  Therefore, this was the initial buildout of the space
• All other Space IDs only have one occurance in the table; therefore they are all initial buildouts. 

 Hopefully that clarifies what I'm trying to solve for.

Thanks for the help!

[ryan_mayu]

@MikDumb 

could you pls show the expected output in excel and provide the screenshot?

[ryan_mayu]

@MikDumb 

what's the expected output?

So the logic is mininum lease date and maximum tenant opening?

maybe try 

=min([table[lease date])

=max(table(tenant opening])