Forum Discussion
Calculate with DateDiff on different row levels
Hi guys,
hope you are doing well. I´m a DAX Beginner so maybe the problem is trivial but at the moment I´m struggling with it.
I have a questions regarding the function DateDiff. I have to create a Vacancy Length for a Rental Unit based on the End Date of an old contract and the Start Date of the new contract. (See picture below)
E. g. 1RU00002 has an contract which is ending on 28.02.2017 and get´s a new contract asigned on 01.10.2017. I need to calculate the difference between, here 7 Month.
I tried to use a measure based on filter for RentalUnit_ID and Min Max Values therefor but this isn´t working well.
Does someone has an idea about it?
Thanks a lot,
Christian
Hi Anonymous ,
As the example you shared, we can create a measure using following DAX to meet your requirement. If you use the month in datediff, it will return 8 month for 1RU00002
VacancyLength = VAR i = SELECTEDVALUE ( Test[RentalUnit_ID] ) VAR t = FILTER ( ALL ( Test ), [RentalUnit_ID] = i ) RETURN IF ( COUNTROWS ( t ) = 2, DATEDIFF ( MINX ( t, [EndDate] ), MAXX ( t, [StartDate] ), DAY ), BLANK () )If it doesn't meet your requirement such as record for a ID is more than two, kindly share your excepted result to me if you don't have any Confidential Information.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
3 Replies
Hi Anonymous ,
As the example you shared, we can create a measure using following DAX to meet your requirement. If you use the month in datediff, it will return 8 month for 1RU00002
VacancyLength = VAR i = SELECTEDVALUE ( Test[RentalUnit_ID] ) VAR t = FILTER ( ALL ( Test ), [RentalUnit_ID] = i ) RETURN IF ( COUNTROWS ( t ) = 2, DATEDIFF ( MINX ( t, [EndDate] ), MAXX ( t, [StartDate] ), DAY ), BLANK () )If it doesn't meet your requirement such as record for a ID is more than two, kindly share your excepted result to me if you don't have any Confidential Information.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it moreHej v-lid-msft ,
thank for your replay and help first! Your formular works quite well to get the needed vacancy rate. I this case fortunately we have only n = 2 equal RentalUnit ID´s to calculate with so for me your solution works fine! Thanks a lot!
But I have one extending question just in case. The requirements in general are that here can be various RentalUnit ID´s based on the historical course of a contract. E. g.
1RU00002 could exist four times
1RU00005 could exist six times and so on.
So how you would solve the problem in this case, because the countrows (t) function would need to more flexible :/
Thank you!
Christian
Hi Anonymous ,
We can change this formula to following to meet your requirement.
VacancyLength2 = VAR i = SELECTEDVALUE ( Test2[RentalUnit_ID] ) VAR t = FILTER ( ALL ( Test2 ), [RentalUnit_ID] = i ) RETURN IF ( COUNTROWS ( t ) > 1, DATEDIFF ( MINX ( t, [StartDate] ), MAXX ( t, [EndDate] ), DAY ) - SUMX ( t, DATEDIFF ( [StartDate], [EndDate], DAY ) ), BLANK () )If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more