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

     

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I think I foudn the error. I have another column for "Well Name". Because there are multiple wells, there's multiple cells for the same date (for example, there will be 2 rows with data on May 5th because there are two wells). 

     

    When I just include one well, your code works (in reality, my full prpoject has hundreds of wells). 

     

    Thanks again for your help, I really appreciate it. 

     

    Here is the file:

     

    https://www.dropbox.com/s/4trp6w44k0a0ly3/Example.pbix?dl=0

     

     

    jdbuchanan71 

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      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)
    )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thank you, I really appreciate your help!

         

        Justin