topic Re: Obtain values of a previous row on the same column in DAX Commands and Tips

Obtain values of a previous row on the same column

ezeanya — Mon, 07 Dec 2020 21:33:54 GMT

I need some help.

I'm trying to calculate the values of the Cal-production. it looks at EI column values and sum of previous results in cal-prod. if the sum of both are > than ROP column, it should display a number 800.

I can do that in Excel (see below), but I'm struggling to get that in Power BI

Cal-production ( in excel) D3 = IF(SUM(D2,B3)>C3,0,800)

	EI	ROP	Cal - production
1/1/2021	1440	1393	0
2/1/2021	1015	1393	800
3/1/2021	714	1393	0
4/1/2021	498	1393	800
5/1/2021	248	1442	0

Re: Obtain values of a previous row on the same column

amitchandak — Tue, 08 Dec 2020 01:40:18 GMT

ezeanya , Create a new column like

new column =
var _1 = maxx(filter(Table1, Table1[Date] < earlier(Table1[Date]) ), [Date])
return
if(maxx(filter(Table1, Table1[Date] =_1 ), [EL]) >[ROP], 800,0)

Re: Obtain values of a previous row on the same column

ezeanya — Tue, 08 Dec 2020 18:30:02 GMT

amitchandak

Thanks for the respond. the ROP data is coming from measure data. So the earlier function is not working when I use this for a new measure. I tried using

New Column =

VAR

var_1 = maxx(filter(ALLSELECTED(Table1[Date]), Table1[Date] < (Table1[Date])), Table1[Date])

return

if(maxx(filter(Table1, Table1[Date] =var_1 ), [EL]) > [ROP], 800,0)

but the results were different.

Re: Obtain values of a previous row on the same column

ezeanya — Tue, 08 Dec 2020 18:36:59 GMT

This is what i get

Excel 0, 800,0,800

DAX 0,800,0,0

Re: Obtain values of a previous row on the same column

ezeanya — Tue, 08 Dec 2020 18:44:40 GMT

amitchandak i forgot to mention that the D4 in the excel will be =IF(SUM(D3,B4,D2)>C4,0,800)

every new cell in a row has to add all the previous results

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)

Till December month