Custom column

Anonymous you have to have an identifier to find the previous row, again as mentioned in the previous post, some index/id/date column to find out the row if you don't have this in the model you can add index column in power query.

Let's be clear, it is not as straight forward as excel where you can refer to a cell.

Anonymous ,

Follow the below steps:

Step 1: create index column from Power Query. Go to Power Query editor --> Select any column on which you want to create index lets say "DateColumn"  --> Go to Add Column menu --> click on Index Column --> Save and apply.

Step 2: Create below DAX Column:

Column =

Var a = CALCULATE(MAX(Sheet6[Value2]),FILTER((Sheet6),Sheet6[Index]=EARLIER(Sheet6[Index])-1))

RETURN IF(a=BLANK(),CALCULATE(SUM(Sheet6[Value]),Sheet6[Index]=0),a)

See the below Screen shot:

Hi, Anonymous 

Based on your description, you need to create an index column in 'Query Editor'. The pbix file is attached in the end.

Then you may create a calculated column and a measurem, or only a measure.

Calculated column:

Column = 
IF(
    [Index]=0,
    [Depth from],
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],[Index]-1)
)

Measure:
Measure 1 = 
IF(
    ISFILTERED('Table'[Depth from]),
    SUM('Table'[Column]),
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],CALCULATE(MAX('Table'[Index]),ALL('Table')))
)

or

Measure 2 = 
var _index = SELECTEDVALUE('Table'[Index])
return
IF(
    ISFILTERED('Table'[Depth from]),
    IF(
        _index=0,
        SELECTEDVALUE('Table'[Depth from]),
        LOOKUPVALUE('Table'[Depth to],'Table'[Index],_index-1)
    ),
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],CALCULATE(MAX('Table'[Index]),ALL('Table')))
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

Create a Calculated Column

Total Depthm =

SWITCH(

TRUE(),

'Table'[stepNo] = 1 , 'Table'[Depth from m],

'Table'[stepNo] = 2, 'Table'[depth to m],

CALCULATE(MAX('Table'[depth to m]),FILTER('Table','Table'[stepNo] < EARLIER('Table'[stepNo]))

) )

 

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)