Forum Discussion

salexa's avatar
salexa
Helper II
5 years ago
Solved

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_IdDateResponsableDesired OutPut ChangeDesired  output LastChanged DateDesired Output Days between Changes
12021/8/2021Name1SameName  
12022/8/2021Name1SameName  
12023/8/2021Name1SameName  
12024/8/2021Name1SameName  
12028/8/2021Name1SameName  
12029/8/2021Name2ChangedName1/8/20218
120210/8/2021Name2SameName  
120211/8/2021Name2SameName  
120212/8/2021Name3ChangedName9/8/20213
1202    13/8/2021Name3SameName  
1202    14/8/2021Name3SameName  
1202    15/8/2021Name3SameName  
1202    16/8/2021Name3SameName  
1202    17/8/2021Name3SameName  
1202    18/8/2021Name3SameName  
1202    19/8/2021Name3SameName  

 

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!

  • 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

  • selimovd's avatar
    selimovd
    Most 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
     
    • salexa's avatar
      salexa
      Helper II

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