Calculate difference between values for same id

[salexa]

Hi,

I'm trying to calculate the difference in days, for the same id, between two values in column Responsable, but only when the Responsable has a new value - the difference between changes of the resposable. A sample input is bellow, Input columns Issue_ID, Date, Responsable:

Issue_Id	Date	Responsable	Desired OutPut Change	Desired  output LastChanged Date	Desired Output Days between Changes
1202	1/8/2021	Name1	SameName
1202	2/8/2021	Name1	SameName
1202	3/8/2021	Name1	SameName
1202	4/8/2021	Name1	SameName
1202	8/8/2021	Name1	SameName
1202	9/8/2021	Name2	ChangedName	1/8/2021	8
1202	10/8/2021	Name2	SameName
1202	11/8/2021	Name2	SameName
1202	12/8/2021	Name3	ChangedName	9/8/2021	3
1202	13/8/2021	Name3	SameName
1202	14/8/2021	Name3	SameName
1202	15/8/2021	Name3	SameName
1202	16/8/2021	Name3	SameName
1202	17/8/2021	Name3	SameName
1202	18/8/2021	Name3	SameName
1202	19/8/2021	Name3	SameName

I've managed to calculate when the change is done:

Desired OutPut Change=

var _PreviousDate=maxx(FILTER(ALLEXCEPT(table,table[Issue_ID]),EARLIER(table[Date])>table[Date]),table[Date])
var _PreviousStatut= CALCULATE(max(table[Responsable]),FILTER(ALLEXCEPT(table,table[Issue_ID]),table[Date]=_PreviousDate))
var _Current= table[Responsable]

Return
if(_Current=_PreviousStatut,"Same","Changed")

But Im struggeling with calculating to return the previous date when the change happend and the difference.

Thanks!

[selimovd · Accepted solution]

Hey @salexa ,

I tried a little bit different approach. 

Check the following calculated column, this should give you the correct date:

Desired  output LastChanged = 
-- save the date of current row in a variable
VAR vRowDate = myTable[Date]
-- calculate first date for current Issue_ID
VAR vFirstDate =
    CALCULATE(
        MIN( myTable[Date] ),
        ALLEXCEPT(
            myTable,
            myTable[Issue_Id]
        )
    )
-- calculate the date of the last change before this row
VAR vLastChangeDate =
    CALCULATE(
        MAX( myTable[Date] ),
        ALLEXCEPT(
            myTable,
            myTable[Issue_Id]
        ),
        myTable[Desired OutPut Change] = "ChangedName",
        myTable[Date] < vRowDate
    )
RETURN
-- return the values for the different cases
    SWITCH(
        TRUE(),
        myTable[Desired OutPut Change] <> "ChangedName", BLANK(),
        vLastChangeDate = BLANK(), vFirstDate,
        vLastChangeDate
    )

And then the amount of days is an easy one:

Desired  output LastChanged Days = DATEDIFF(myTable[Desired  output LastChanged], myTable[Date], DAY)

I did some comments in the measure in order to understand better what the measure is doing.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[salexa]

thanks for the help, with your formula I have the desired results 😉