topic Re: Dax to crack scenario - previous row and apply results in same row in DAX Commands and Tips

Dax to crack scenario - previous row and apply results in same row

msbiuser1 — Thu, 13 Oct 2022 06:25:17 GMT

Hi All,

We have a scenario where we need to loop the calculation in same column. Following is the sample data:

The column C will have only first row filled and here its 3.

The below rows for column C is as follows:

Value of Previour row in Column C +Current row of Column A - Current row of Column B

ie to get 2nd row = 3+5-6 = 2

to get 3rd row = 2+9-3 = 8

to get 4th row =8+1 -3 =6

to get 5th row = 6+4-5 = 5

Kindly advice me to crack this.
Thanks in advance.

RichardOfSydney — Thu, 13 Oct 2022 06:41:25 GMT

you will need an index column , use that to find the previous row.

msbiuser1 — Thu, 13 Oct 2022 06:51:51 GMT

I did that. Was able to bring 3 in second row. But how to handle for remaining rows.There we get blanks

RichardOfSydney — Thu, 13 Oct 2022 06:56:47 GMT

lookupvalue use index-1 to get pervious row

msbiuser1 — Thu, 13 Oct 2022 12:50:29 GMT

Really appreciate if you can guide us with measure

saravanan_p — Thu, 13 Oct 2022 12:12:08 GMT

Hi its tricky and here are the results:

We have to split into 3 measures:

m1 : get that 3 in all rows in a column

m2: get a column with column A minus B where sheet C has blank

m3: now add m1 +m2 You get the results as desired.

m1 m2 m3

3 3

3 -1 2

3 5 8

3 3 6

saravanan_p — Thu, 13 Oct 2022 12:12:47 GMT

It doesnt work brother. If works update the dax. Let us know