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.
There should be an easy fix for this, but I'm not quite sure how to do it.
The data is simple: Name, date, value:
Bob | 01-01-2020 | 99 |
Bob | 01-03-2020 | 98 |
Bob | 01-05-2020 | 96 |
Jane | 02-01-2020 | 77 |
Jane | 02-02-2020 | 76 |
Jane | 02-03-2020 | 78 |
Kenneth | 01-06-2020 | 66 |
Because the difference for Bob's last two values is >= 2, I need to flag it somehow. Jane and Kenneth don't need anything, because Jane's last 2 values have a difference of -2 and since it's a rise it doesn't need to be flagged, and Kenneths value can't be compared to anything because there's only the one.
It's ONLY the last two values that are important. For example if Bob gets updated on 01-06-2020 and the value is 95, he no longer needs to be flagged.
The end result should be a list of names that are flagged.
I'd appreciate any help I can get, thanks!
Solved! Go to Solution.
Hi @grggmrtn ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @grggmrtn ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@grggmrtn , Create a new column that will flag for that row, You can filter a get name
Column = var _1 = maxx(FILTER('Table','Table'[Name]= EARLIER('Table'[Name]) && 'Table'[Date] < EARLIER('Table'[Date])),LASTNONBLANKVALUE('Table'[Date],'Table'[Value])) return If(ISBLANK(_1), BLANK(),if( _1 -[Value] >=2,1,0))
file is attached after signature
Thanks for this @amitchandak - it's almost exactly what I'm looking for 🙂
The problem comes since your column is looking at all dates instead of just the last two. Here's an example from my data (the colours indicate three different names in this column)
If I filter this list by [Column] then "blue" will also be in this list, even though the difference between their values of the last two dates < 0. So my flags won't tell me what the situation is TODAY when I'm looking at the report. According to the above data, only "yellow" should the only one that has a 1.
Make any sense?
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |