Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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) )
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 ) )
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 ) )
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?
I'm trying to add this in a table in the model ("New Column" from the "Modeling" ribbon). Not a measure.
Thanks again,
Justin
@Anonymous , It works for me in my testing. Can you share you .pbix file?
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
@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 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
Thank you, I really appreciate your help!
Justin