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,
can't find a solution for callculation of increments based on few conditions.
Current table example in my Power Query:
date | person | performance |
4.4.2020 | A | 10 |
4.4.2020 | B | 12 |
4.4.2020 | C | 8 |
4.4.2020 | D | 15 |
5.4.2020 | A | 12 |
5.4.2020 | B | 16 |
5.4.2020 | C | 10 |
5.4.2020 | D | 5 |
6.4.2020 | A | 9 |
6.4.2020 | B | 8 |
6.4.2020 | C | 11 |
6.4.2020 | D | 7 |
7.4.2020 | A | 6 |
7.4.2020 | B | 4 |
7.4.2020 | C | 20 |
7.4.2020 | D | 11 |
Expected solution:
I need calculate the increment of column "performance" based on previous date value include column "person" evaluation:
date = 4.4.2020 for person = A there is performance = 10
date = 5.4.2020 for person = A there is performance = 12
then increment is = 2
date | person | performance | Increment |
4.4.2020 | A | 10 | 0 |
4.4.2020 | B | 12 | 0 |
4.4.2020 | C | 8 | 0 |
4.4.2020 | D | 15 | 0 |
5.4.2020 | A | 12 | 2 |
5.4.2020 | B | 16 | 4 |
5.4.2020 | C | 10 | 2 |
5.4.2020 | D | 5 | -10 |
6.4.2020 | A | 9 | -3 |
6.4.2020 | B | 8 | -8 |
6.4.2020 | C | 11 | 1 |
6.4.2020 | D | 7 | 2 |
7.4.2020 | A | 6 | -3 |
7.4.2020 | B | 4 | -4 |
7.4.2020 | C | 20 | 9 |
7.4.2020 | D | 11 | 4 |
Thx for a help.
J.
Solved! Go to Solution.
So basically, as in the article referenced, something like:
Increment Column =
VAR __Current = [performance]
VAR __PreviousDate = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] < EARLIER([date])),[date])
VAR __Previous = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] = __PreviousDate),[performance])
RETURN
__Current - __Previous
@jeyare ,
Try like
diff =
var _last = maxx(filter(Table,table[person]=earlier(table[person]) && table[Date] = earlier(table[Date])-1),table[performance])
return
if(isblank(_last),0,performance-_last)
So basically, as in the article referenced, something like:
Increment Column =
VAR __Current = [performance]
VAR __PreviousDate = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] < EARLIER([date])),[date])
VAR __Previous = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] = __PreviousDate),[performance])
RETURN
__Current - __Previous
thx, seems to be fine until first row, when I get an error:
The value for 'performance' cannot be determined. Either 'performance' doesn't exist, or there is no current row for a column named 'Tperformance'.
Then IF needs to be there, but not sure where
Hmm, see attached PBIX. Can't say what went wrong but the PBIX seems to work fine.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |