Show values based on latest start Date

[jagjit_singh]

Hi All,

I have the attached data and need to transform it to show the values based on the latest tenant lease date (commencement_date). Every year the lease is renewed and want to show only details of the lastest lease. In the attached excel file, the Required_Result Tab shows the end result desired.

Thanks

Jag

[patrick_digan · Accepted solution]

@jagjit_singh My solution involves transposing the data and applying filters and then joins to limit the data down to what you're looking for before using a crosstab at the end to get it back to the correct orientation. I didn't calculate the Latest Year or Days to Expire. Let me know if you have any questions!