Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |