Calculate values using previous row on the same column

[cceze]

Hi,

I'm trying to calculate the values of the Cal-production in Power BI ( Like Production Sales Inventory model) . The calculation looks at EI (ending inventory) column values and compares it to the ROP. IF the EI + previous results from Cal-production is greater than ROP, it displays 0 else 800.

In Excel have something like this:

Cl-production column is on Column D
D2 = =IF(sum(B2)>C2,0,800)

D3 = IF(SUM(D2,B3)>C3,0,800)

D4 = IF(SUM(D3,B4,D2)>C4,0,800)

D5 = =IF(SUM(D4,B5,D3,D2)>C5,0,800)

	EI	ROP	Cal-Production (results)
1/1/2021​	1,440	1393​	0​
2/1/2021​	1,015	1393​	800​
3/1/2021​	714	1393​	0​
4/1/2021​	498	1393​	800​
5/1/2021​	248	1442​	0​
6/1/2021​	(39)	1442​	0​
7/1/2021​	(399)	1442​	800​
8/1/2021​	(819)	1442​	0​
9/1/2021​	(1,299)	2005​	800​
10/1/2021​	(1,809)	2005​	800​

 

[maabadi]

In Excel Also you can use this (At D2) & then drag it down:

=IF(ROW()=2,IF(SUM($B$2)>$C$2,0,800),IF(SUM(B2,$D$1:D1)>C2,0,800))

 

[cceze]

In Excel Also you can use this (At D2) & then drag it down:

=IF(ROW()=2,IF(SUM($B$2)>$C$2,0,800),IF(SUM(B2,$D$1:D1)>C2,0,800))

Thanks for the Excel tip! I made that changes and it worked. Do you know if this is something that I can do on Power BI?

 

[maabadi]

Sorry, I don't work with PowerBI. Maybe others can Help.

 

[Purplehazed]

cceze, not sure I can or should be giving anybody DAX advice because I am leaning myself but here is my best answer...

Look at the EARLIER DAX function. I think EARLIER can compare a value in a row to a previous row value in the the same column. I believe an optional syntax argument is a number that can be used to tell DAX to look at the previous row or skip rows in a pattern. I expect most users would compare to the previous row and that is default and why the argument is optional.

If I am wrong, apologies for jumping in as a novice myself. I have not used EARLIER, just read about in the Russo's purple DAX book.

 

[cceze]

cceze, not sure I can or should be giving anybody DAX advice because I am leaning myself but here is my best answer...

Look at the EARLIER DAX function. I think EARLIER can compare a value in a row to a previous row value in the the same column. I believe an optional syntax argument is a number that can be used to tell DAX to look at the previous row or skip rows in a pattern. I expect most users would compare to the previous row and that is default and why the argument is optional.

If I am wrong, apologies for jumping in as a novice myself. I have not used EARLIER, just read about in the Russo's purple DAX book.

Thanks!. I will try that