Previous Row Value in Calculated Column, not Measure

[Anonymous]

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. 

[jdbuchanan71]

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]

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]

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]

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]

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

[Anonymous]

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 · Accepted solution]

@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]

@jdbuchanan71 Thanks again for helping me to solve my earlier problem. I'm trying to sum the  [OIL_AP_DISP_CAT] per well in a column and I think it's going to be a similiar function right? 

Is it possible to sum the total [OIL_AP_DISP_CAT] per well (it would just duplicate in the column for every date per well). I don't need to refer to the previous date like before. 

Thanks again, I really appreciate yoru support. 

Justin 

[Anonymous]

Thank you, I really appreciate your help!

Justin