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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IHam
Helper III
Helper III

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

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

 

 

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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 

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

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

 

 

 

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

@IHam

No worries. glad it helped.

Perhaps some kudos then? Smiley Tongue

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.