DATEDIFF Difference from two different rows and column

[Anonymous]

Hello everyone,

I  have a table like below-

property_id	Contract_id	start_date	end_date	State
100	100.03	15.04.2019		Confirmed
100	100.02	01.01.2015	30.09.2018	Expired
100	100.01	15.02.2010	31.09.2017	Expired

Now I need to find how long the apartment was vacant. Since the property_id is same for both the rows, it is the same apartment and end_date(100.01) minus start_date(100.02) gives me the duration for which this apartment was vacant. Which is in this case equals 197 days.

Could someone please help me to find this. I created a rank column using below DAX whic give me 1 for the first row and 2 for the next row in this example.

Ranks Col = RANKX (

FILTER ( 'table', 'table'[property_id] = EARLIER ('table'[property_id] ) ),

'table'[start_date],

,

DESC,

DENSE

)

[AlB · Accepted solution]

Hi @Anonymous ,

Are there always two rows per property?

If so you can create a new calculated column:

NewCol =
VAR  _Date1 = CALCULATE(MIN(Table1[end_date]), Table1[end_date]<> BLANK() ,ALLEXCEPT(Table1, Table1[property_id]))
VAR  _Date2 = CALCULATE(MAX(Table1[start_date]), ALLEXCEPT(Table1, Table1[property_id]))
RETURN
_Date2 - _Date1

[Anonymous]

Hi @AlB ,

Thank you for your response. I will give it a try.

And regarding your question, No it could be more than two rows and even one row only as well (if there is only one contract till now for that apartment).

Best,

Ashu