cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jkilb Regular Visitor
Regular Visitor

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. 

 

Previous Row.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Previous Row Value in Calculated Column, not Measure

@Jkilb 

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

 

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Previous Row Value in Calculated Column, not Measure

hello @Jkilb ,

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 )
    )
Jkilb Regular Visitor
Regular Visitor

Re: Previous Row Value in Calculated Column, not Measure

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

 

 

previous row 2.PNG

 

Super User
Super User

Re: Previous Row Value in Calculated Column, not Measure

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?  

Jkilb Regular Visitor
Regular Visitor

Re: Previous Row Value in Calculated Column, not Measure

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 

Super User
Super User

Re: Previous Row Value in Calculated Column, not Measure

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

Jkilb Regular Visitor
Regular Visitor

Re: Previous Row Value in Calculated Column, not Measure

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 

Super User
Super User

Re: Previous Row Value in Calculated Column, not Measure

@Jkilb 

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

 

View solution in original post

Jkilb Regular Visitor
Regular Visitor

Re: Previous Row Value in Calculated Column, not Measure

Thank you, I really appreciate your help!

 

Justin 

Jkilb Regular Visitor
Regular Visitor

Re: Previous Row Value in Calculated Column, not Measure

@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 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 48 members 846 guests
Please welcome our newest community members: