Forum Discussion

ktb2296's avatar
ktb2296
Frequent Visitor
5 years ago

DAX: Referencing the previous row within the same Calculated Column

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...
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

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  !!

  • ktb2296's avatar
    ktb2296
    Frequent Visitor
    5 years ago

    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.