Calculating difference between dates

[Anonymous]

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

[v-zhenbw-msft]

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.

[v-zhenbw-msft · Accepted solution]

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.

[mahoneypat]

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 )

)

[Anonymous]

its not working for my i only get null values