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.
Hello,
I’m trying to calculate the below field(Results) on PBI( query editor ). Calculation should be grouped by CustomerCode, ITEM, TYPE and the first row of the group should be calculated as Z1+X1-X2 then 2nd up to the last row of the group should be previous result+X1-X2. Let me know if you have any solutions in mind. Thanks in advance
1st row = Z1+ X1– X2 ; 15-0-4 = 11
2nd row = prevresult(1st row) + X1 – X2 ; 11+0-1 = 10
WeekStart | CustomerCode | ITEM | Department | Type | Z1 | X1 | X2 | Result | Formula | ||
9/28/2020 | AAA | Apple | FIN | A | 15 | 4 | 11 | Z1 - X1 + X2 | 15 + 0 - 4 = 11 | ||
10/5/2020 | AAA | Apple | FIN | A | 15 | 1 | 10 | PrevResult + X1 - X2 | 11 + 0 - 1 = 10 | ||
11/2/2020 | AAA | Apple | FIN | A | 15 | 3 | 13 | PrevResult + X1 - X2 | 10 + 3 - 0 =13 | ||
10/12/2020 | AAA | Apple | OPS | B | 10 | 1 | 9 | Z1 - X1 + X2 | 10 + 0 - 1 = 9 | ||
10/19/2020 | AAA | Apple | OPS | B | 10 | 2 | 7 | PrevResult + X1 - X2 | 9 + 0 - 2 = 7 | ||
11/2/2020 | AAA | Apple | OPS | B | 10 | 1 | 6 | PrevResult + X1 - X2 | 7 + 0 - 1 = 6 | ||
9/21/2020 | AAA | Banana | OPS | A | 13 | 1 | 14 | Z1 - X1 + X2 | 13 + 1 - 0 = 14 | ||
10/5/2020 | AAA | Banana | OPS | A | 13 | 1 | 13 | PrevResult + X1 - X2 | 14 + 0 - 1 = 13 | ||
10/12/2020 | AAA | Banana | OPS | A | 13 | 1 | 3 | 11 | PrevResult + X1 - X2 | 13 + 1 - 3 = 11 |
Solved! Go to Solution.
You can use DAX to create a column (not in query editor)
Column =
VAR _start=CALCULATE(min('Table'[WeekStart]),ALLEXCEPT('Table','Table'[CustomerCode],'Table'[ITEM],'Table'[Type]))
VAR Z1= MAXX(FILTER('Table','Table'[WeekStart]=_start&&'Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Z1])
VAR X1=SUMX(FILTER('Table','Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[WeekStart]<=EARLIER('Table'[WeekStart])),'Table'[X1])
VAR X2=SUMX(FILTER('Table','Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[WeekStart]<=EARLIER('Table'[WeekStart])),'Table'[X2])
RETURN Z1+X1-X2
Proud to be a Super User!
You can use DAX to create a column (not in query editor)
Column =
VAR _start=CALCULATE(min('Table'[WeekStart]),ALLEXCEPT('Table','Table'[CustomerCode],'Table'[ITEM],'Table'[Type]))
VAR Z1= MAXX(FILTER('Table','Table'[WeekStart]=_start&&'Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Z1])
VAR X1=SUMX(FILTER('Table','Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[WeekStart]<=EARLIER('Table'[WeekStart])),'Table'[X1])
VAR X2=SUMX(FILTER('Table','Table'[CustomerCode]=EARLIER('Table'[CustomerCode])&&'Table'[ITEM]=EARLIER('Table'[ITEM])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[WeekStart]<=EARLIER('Table'[WeekStart])),'Table'[X2])
RETURN Z1+X1-X2
Proud to be a Super User!
you are welcome!
Proud to be a Super User!
Let me preface this that I am still somewhat new to PBI but I think I know how to do what you need.
To do this you'll first need to add an index column (I prefer to start from 0).
Once you have your index column, you can create a new calculated column use a DAX expression as follows:
Column = LOOKUPVALUE('*TableName*'[Result], '*TableName*'[Index], '*TableName*'[Index]-1)+0
You may need to change some of the syntax to get it to work. And obviously for the first row, since you can't pull data from a previous row (since there isn't one), you may need to put the expression inside an IF expression.
There may be a better way to do it but I think that should at least work.
thank you for answering, but if you will notice every start of the group the formula is Z1 + X1 -X2.
on the first row the formula is Z1 + X1 -X2
succeeding row until the end of the group is previousresult(1st row) + X1 - X2
WeekStart | CustomerCode | ITEM | Department | Type | Z1 | X1 | X2 | Result | Formula | ||
9/28/2020 | AAA | Apple | FIN | A | 15 | 4 | 11 | Z1 - X1 + X2 | 15 + 0 - 4 = 11 | ||
10/5/2020 | AAA | Apple | FIN | A | 15 | 1 | 10 | PrevResult + X1 - X2 | 11 + 0 - 1 = 10 | ||
11/2/2020 | AAA | Apple | FIN | A | 15 | 3 | 13 | PrevResult + X1 - X2 | 10 + 3 - 0 =13 | ||
10/12/2020 | AAA | Apple | OPS | B | 10 | 1 | 9 | Z1 - X1 + X2 | 10 + 0 - 1 = 9 | ||
10/19/2020 | AAA | Apple | OPS | B | 10 | 2 | 7 | PrevResult + X1 - X2 | 9 + 0 - 2 = 7 | ||
11/2/2020 | AAA | Apple | OPS | B | 10 | 1 | 6 | PrevResult + X1 - X2 | 7 + 0 - 1 = 6 |
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |