Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |