DAX calculated number of days between dates in the same column

[luxpbi]

Hi, 

I'm struggling with a problem in DAX. 

I have a data that looks like this:

Date	Product	State
01/01/2018	1	A
05/01/2018	1	B
12/01/2018	1	C
15/01/2018	1	D
17/01/2018	1	E
04/01/2018	2	B
06/01/2018	2	D
08/01/2018	2	A
02/01/2018	3	A

I need to calculate for the same product the number of days between 2 states.

And in case it's possible only the number of days that a product is in state let's say D. 

Thanks for your help, I'm not sure how to aboard the problem.

[Stachu]

try this code for calculated column

Column =
VAR CurrentProduct = 'Table'[Product]
VAR CurrentState = 'Table'[State]
VAR CurrentDate = 'Table'[Date]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Product] = CurrentProduct
                && 'Table'[State] <> CurrentState
                && 'Table'[Date] > CurrentDate
        )
    )
RETURN
    IF ( ISBLANK ( NextStateDate ), BLANK (), NextStateDate - CurrentDate )

[luxpbi]

Hi @Stachu, 

Thank you for your reply, it's almost what I'm looking for.
It doesn't work when for one product there is 2 lines with the same date but diferent status. Is possible to fix it?

13/05/2018 0:00	D	6
13/05/2018 0:00	A	6
19/05/2018 0:00	D	1

For the first line here the value should be 1 and for the second is correct. I've noticed that this occur a lot of times.

Do you think it's also possible to do it only for one kind of status (for example for status = D)? 

Thanks for your help!

[Stachu]

do you mean something like this?

Column =
VAR CurrentProduct = 'Table'[Product]
VAR CurrentState = 'Table'[State]
VAR CurrentDate = 'Table'[Date]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Product] = CurrentProduct
                && 'Table'[State] <> CurrentState
                && 'Table'[Date] > CurrentDate
        )
    )
RETURN
    IF (
        CurrentState = "D",
        IF ( ISBLANK ( NextStateDate ), 1, NextStateDate - CurrentDate ),
        BLANK ()
    )

[luxpbi]

Just one more step 🙂 

In my case finally I just don't need to count the number of days for a product for one type of status but for 2 of them. 

Date	Status	Column
11/01/2018	8	26
11/01/2018	2
11/01/2018	9	26
05/02/2018	2

My issue is that the first value should be 1 and the second 26. 

Any aditional great idea?

Thank you!

[Stachu]

can you share the data that shows that in the format of the table from your first post?
The Status column wasn't there, and it's difficult to make sense out of it

[luxpbi]

Sorry I just did it manually. 

Date	Product	State	Column
11/01/2018	1	D	26
11/01/2018	1	A
11/01/2018	1	C	26
05/02/2018	1	A

 And I need to filter by D and C, not just D. 

And the first value should be 1 and the second 26. 

[Stachu]

for C&D this will work

Column =
VAR CurrentProduct = 'Table'[Product]
VAR CurrentState = 'Table'[State]
VAR CurrentDate = 'Table'[Date]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Product] = CurrentProduct
                && 'Table'[State] <> CurrentState
                && 'Table'[Date] > CurrentDate
        )
    )
RETURN
    IF (
        CurrentState = "D"
            || CurrentState = "C",
        IF ( ISBLANK ( NextStateDate ), 1, NextStateDate - CurrentDate +1),
        BLANK ()
    )

by default it retuned 25, so I aded +1 (in red in the code, so ou can remove it if needed), it also makes sense as you consider same date to be 1 and not 0

[luxpbi]

Yes that work perfect but I have this:

Row	Date	Product	State	Column
1	11/01/2018	1	D	26
2	11/01/2018	1	A
3	11/01/2018	1	C	26
4	05/02/2018	1	A

  And what I need is this: 

Row	Date	Product	State	Column
1	11/01/2018	1	D	1
2	11/01/2018	1	A
3	11/01/2018	1	C	26
4	05/02/2018	1	A

Row 1 should be 1 and not 26 because the product is in state D one day and then changes to state C.