topic Re: DAX: Referencing the previous row within the same Calculated Column in DAX Commands and Tips

DAX: Referencing the previous row within the same Calculated Column

ktb2296 — Thu, 12 Aug 2021 13:01:34 GMT

I am currently trying to create a calculated column whereby the first value in the row is the basis for the calculation, and the rest of the values in the column are built off of this value.

I am trying to understand if this is even possible in Power BI, but because it is doable in Excel, I would imagine that there is some sort of solution to this query.

In the screeshot below, you'll notice the starting value for the calculated column needs to be the value that the arrow is pointing to (this will be a static value that I can plug in using the associated date, May 2016, as the parameter), which then needs to be used to build the value in the next row and so forth.

Hoping someone can offer some advice or shed some light on how possible it is. Thanks in advance!

Re: DAX: Referencing the previous row within the same Calculated Column

VahidDM — Fri, 13 Aug 2021 05:57:14 GMT

Hi ktb2296

I'm not sure how do you want to use the First row value to calculate the other columns/rows value, but to find the first value in the column you can use the "FIRSTNONBLANK" formula in DAX.

For your model you can use this code:

First Value = FIRSTNONBLANK ( 'Table (2)'[Revised rent], MAX ( 'Table (2)'[Revised rent] ) )

the output will be like below, and you can use that measure to create that calculated column:

As an another example, to find the first value in the Monthly Average column, use the below code:

because the first row in that column is empty, it shows the second row.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

Re: DAX: Referencing the previous row within the same Calculated Column

ktb2296 — Fri, 13 Aug 2021 13:32:34 GMT

HI Vahid,

This doesn't really answer the full query. Essentially the first row, or May 2016, will be 135 (Variable x)

Then to get the next value for June 2016, in the same column, I need to use this equation:

1-ABS(0.5-NORM.Dist( Monthly Average , Variable X, Measure 1, TRUE) = Variable Y

Then to get the value for July 2016:

1-ABS(0.5-NORM.Dist( Monthly Average , Variable Y, Measure 1, TRUE)

This needs to continue on for the whole calculated column. So that's why I'm wondering if it's possible to calculate a column where each row will need to take the previous calculated row's value.

Re: DAX: Referencing the previous row within the same Calculated Column

Anonymous — Tue, 17 Aug 2021 20:16:37 GMT

Hello , were you able to solve this problem. I am in a similar boat and have been clinging to this problem since weeks.