cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calculated field based on previous row

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

 

WeekStartCustomerCodeITEMDepartmentTypeZ1X1X2Result Formula
9/28/2020AAAAppleFINA15 411 Z1 - X1 + X215 + 0 - 4 = 11
10/5/2020AAAAppleFINA15 110 PrevResult + X1 - X211 + 0 - 1 = 10
11/2/2020AAAAppleFINA153 13 PrevResult + X1 - X210 + 3 - 0 =13
10/12/2020AAAAppleOPSB10 19 Z1 - X1 + X210 + 0 - 1 = 9
10/19/2020AAAAppleOPSB10 27 PrevResult + X1 - X29 + 0 - 2 = 7
11/2/2020AAAAppleOPSB10 16 PrevResult + X1 - X27 + 0 - 1 = 6
9/21/2020AAABananaOPSA131 14 Z1 - X1 + X213 + 1 - 0 = 14
10/5/2020AAABananaOPSA13 113 PrevResult + X1 - X214 + 0 - 1 = 13
10/12/2020AAABananaOPSA131311 PrevResult + X1 - X213 + 1 - 3 = 11

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Calculated field based on previous row

@naitx23 

You can use DAX to create a column (not in query editor)

1.PNG

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

2.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

5 REPLIES 5
Highlighted
Helper III
Helper III

Re: Calculated field based on previous row

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.

Highlighted
Regular Visitor

Re: Calculated field based on previous row

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

 

 

WeekStartCustomerCodeITEMDepartmentTypeZ1X1X2Result Formula
9/28/2020AAAAppleFINA15 411 Z1 - X1 + X215 + 0 - 4 = 11
10/5/2020AAAAppleFINA15 110 PrevResult + X1 - X211 + 0 - 1 = 10
11/2/2020AAAAppleFINA153 13 PrevResult + X1 - X210 + 3 - 0 =13
10/12/2020AAAAppleOPSB10 19 Z1 - X1 + X210 + 0 - 1 = 9
10/19/2020AAAAppleOPSB10 27 PrevResult + X1 - X29 + 0 - 2 = 7
11/2/2020AAAAppleOPSB10 16 PrevResult + X1 - X27 + 0 - 1 = 6
Highlighted
Super User II
Super User II

Re: Calculated field based on previous row

@naitx23 

You can use DAX to create a column (not in query editor)

1.PNG

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

2.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Regular Visitor

Re: Calculated field based on previous row

@ryan_mayu  Thank you so much! This is exactly what I need!

 

Highlighted
Super User II
Super User II

Re: Calculated field based on previous row

@naitx23 

you are welcome!

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors