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 |
| 1 | A | 0 | 15/01/2019 |
| 2 | A | 0 | 15/01/2019 |
| 3 | B | 1 | 15/01/2019 |
| 1 | A | 1 | 01/02/2019 |
| 2 | A | 1 | 01/02/2019 |
| 3 | B | 1 | 01/02/2019 |
ID has to be the same and the condition is if HC is 0 then i need to calculate the difference in dates until its 1 for the same ID, this condition can happen multiple times over the year and I need to calculate the difference in every ocation
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.
4 Replies
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)RETURNIF (OR ( ISBLANK ( next1date ), thisHCvalue <> 0 ),0,DATEDIFF ( thisdate, next1date, DAY ))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.
Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
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.
