cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IHam Regular Visitor
Regular Visitor

Difference between last two entries in series

Hi, I have the following data, which is a long list of percentile values in a time series which are particular to an identifier. I wish to create a measure which will show if a percentile value has dropped dramatically since the last entry for that item. So i need to create a measure which calculates the difference between the last "number time" and the previous one (this varies depending on the identifier), to see if this difference is below a cut off value.

So for instance it would return a value of 0.9 for 54310 and -10.5 for 54319

Any help would be great.

Thanks

Ian

IdentifierPercentileNumber Time
543102638
5431042.444
5431033.346
5431044.248
5431928.137
5431912.244
5431917.546
54319750
5433514.937
5433534.344
5433517.546
5433536.850
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Difference between last two entries in series

Hi @IHam

 

I assume that in your initial post the 0.9 for 54310 is a typo and should be 10.9 

You could set [Identifier] in the rows of a matrix and the measure below in values.

Let me know if this helps.

 

DeltaLastEntries =
VAR Last_Time =
    MAX ( Table1[Number Time] )
VAR Previous2Last_Time =
    CALCULATE ( MAX ( Table1[Number Time] ); Table1[Number Time] < Last_Time )
VAR Last_Percentile =
    CALCULATE (
        SELECTEDVALUE ( Table1[Percentile] );
        Table1[Number Time] = Last_Time
    )
VAR Previous2Last_Percentile =
    CALCULATE (
        SELECTEDVALUE ( Table1[Percentile] );
        Table1[Number Time] = Previous2Last_Time
    )
RETURN
    Last_Percentile - Previous2Last_Percentile

 

 

 

5 REPLIES 5
Super User
Super User

Re: Difference between last two entries in series

Hi @IHam

 

Does the 'Number Time' column determine the order you want to follow? Is it unique for a given identifier?

It seems so but I'd like confirmation. Then we could use it as a sort of index 

IHam Regular Visitor
Regular Visitor

Re: Difference between last two entries in series

Hi, yes the number time is the number order, so 50 is a later entry than 46. Each identifier will have a unique number time value as well. Many thanks for any help in advance

Super User
Super User

Re: Difference between last two entries in series

Hi @IHam

 

I assume that in your initial post the 0.9 for 54310 is a typo and should be 10.9 

You could set [Identifier] in the rows of a matrix and the measure below in values.

Let me know if this helps.

 

DeltaLastEntries =
VAR Last_Time =
    MAX ( Table1[Number Time] )
VAR Previous2Last_Time =
    CALCULATE ( MAX ( Table1[Number Time] ); Table1[Number Time] < Last_Time )
VAR Last_Percentile =
    CALCULATE (
        SELECTEDVALUE ( Table1[Percentile] );
        Table1[Number Time] = Last_Time
    )
VAR Previous2Last_Percentile =
    CALCULATE (
        SELECTEDVALUE ( Table1[Percentile] );
        Table1[Number Time] = Previous2Last_Time
    )
RETURN
    Last_Percentile - Previous2Last_Percentile

 

 

 

IHam Regular Visitor
Regular Visitor

Re: Difference between last two entries in series

That's perfect, many thanks for all your efforts and expertise - most appreciated!

Super User
Super User

Re: Difference between last two entries in series

@IHam

No worries. glad it helped.

Perhaps some kudos then? Smiley Tongue