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 solutio...
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)
    )

     

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?  

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I'm trying to add this in a table in the model ("New Column" from the "Modeling" ribbon). Not a measure. 

       

      Thanks again, 

      jdbuchanan71 

       

      Justin 

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Anonymous , It works for me in my testing.  Can you share you .pbix file?