Forum Discussion
Solved
Creating New Column in Data Set: Commercial Real Estate
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 b...
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"))
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!