Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Row Value in Calculated Column, not Measure

Hi, 

 

I'm simply trying to reference a previous row using a calculated column rather than a measure. I've seen a lot of solutions using measures, but not many using columns (and the column solutions have not worked for me). 

 

I would like to replicate the results in the "Calculated Column" below. 

 

I will mark the solution - thank you in advance. 

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Anonymous 

    Perfect, thank you for the sample file.  We just need to incorporate the WELL_NAME into our calculation.

    Calculated Column = 
VAR CurrentRow = 'ANADARKO_MV'[DAY_DATE]
VAR CurrentWell = ANADARKO_MV[WELL_NAME]
VAR PreviousDate =
    CALCULATE (
        MAX ( 'ANADARKO_MV'[DAY_DATE] ),
        FILTER ( ALL ( 'ANADARKO_MV' ), 'ANADARKO_MV'[DAY_DATE] < CurrentRow && ANADARKO_MV[WELL_NAME] = CurrentWell)
    )
RETURN
    CALCULATE (
        SELECTEDVALUE( 'ANADARKO_MV'[OIL_AP_DISP_CAT] ),
        FILTER ( ALL ( 'ANADARKO_MV' ), 'ANADARKO_MV'[DAY_DATE] = PreviousDate && ANADARKO_MV[WELL_NAME] = CurrentWell)
    )

     

9 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    hello Anonymous ,

    Give this a try

    Column = 
VAR CurrentRow = 'Table'[Day_date]
VAR PreviousDate =
    CALCULATE (
        MAX ( 'Table'[Day_date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Day_date] < CurrentRow )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE( 'Table'[Oil] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Day_date] = PreviousDate )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your response, I appreciate the quick reply. Unfortunately I'm just getting blank values. Here is the code with the full variables and table name: 

      Calculated Column = 
VAR CurrentRow = 'ANADARKO_MV'[DAY_DATE]
VAR PreviousDate =
    CALCULATE (
        MAX ( 'ANADARKO_MV'[DAY_DATE] ),
        FILTER ( ALL ( 'ANADARKO_MV' ), 'ANADARKO_MV'[DAY_DATE] < CurrentRow )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE( 'ANADARKO_MV'[OIL_AP_DISP_CAT] ),
        FILTER ( ALL ( 'ANADARKO_MV' ), 'ANADARKO_MV'[DAY_DATE] = PreviousDate )
    )

       

       

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Are you adding this as a new column in a table in the model or trying to write this as a measure in a matrix visual?