Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
salexa
Helper II
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_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!

1 ACCEPTED SOLUTION
selimovd
Super User
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
 

View solution in original post

2 REPLIES 2
selimovd
Super User
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
 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.