cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Calculate difference between values for same id

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!

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper II

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors