Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Identifier | Percentile | Number Time |
54310 | 26 | 38 |
54310 | 42.4 | 44 |
54310 | 33.3 | 46 |
54310 | 44.2 | 48 |
54319 | 28.1 | 37 |
54319 | 12.2 | 44 |
54319 | 17.5 | 46 |
54319 | 7 | 50 |
54335 | 14.9 | 37 |
54335 | 34.3 | 44 |
54335 | 17.5 | 46 |
54335 | 36.8 | 50 |
Solved! Go to 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
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |