Forum Discussion
Calculating difference between dates
Hello, I need to calculate the difference between dates if certain conditions are met, sample data: ID Category HC Date 1 A 0 01/01/2019 2 A 1 01/01/2019 3 B 1 01/01/2019 ...
Hi Anonymous ,
We can use the following measure to meet your requirement.
Days Until 1 = VAR thisdate = MAX ( 'Table'[Date] ) VAR thisid = MAX ( 'Table'[ID] ) VAR thisHCvalue = MIN ( 'Table'[HC] ) VAR next1date = CALCULATE ( MAX ( 'Table'[Date] ), FILTER(ALLSELECTED ( 'Table' ), 'Table'[ID]=thisid && 'Table'[HC]=0 )) RETURN IF ( OR ( ISBLANK ( next1date ), thisHCvalue <> 0 ), 0, DATEDIFF ( thisdate, next1date, DAY ) )The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
It will be helpful if you can show us the exact expected result.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this measure expression in a table with Date and ID columns. It will get the shown result.
Days Until 1 =
VAR thisdate =
SELECTEDVALUE ( HC[Date] )
VAR thisid =
SELECTEDVALUE ( HC[ID] )
VAR thisHCvalue =
MIN ( HC[HC] )
VAR next1date =
CALCULATE (
MAX ( HC[Date] ),
ALL ( HC ),
VALUES ( HC[ID] ),
HC[Date] > thisdate,
HC[HC] = 1
)
RETURN
IF (
OR ( ISBLANK ( next1date ), thisHCvalue <> 0 ),
0,
DATEDIFF ( thisdate, next1date, DAY )
)
its not working for my i only get null values