Forum Discussion
salexa
4 years agoHelper 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 th...
- 4 years ago
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
selimovd
4 years agoMost Valuable Professional
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