Forum Discussion

salexa's avatar
salexa
Helper II
4 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 th...
  • selimovd's avatar
    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 regards
    Denis