A calculated column returning a specific value from an "internal table"

[Bjorn_C]

Hello,
can someone please help me to ride a DAX formula in order to determine the value of an additional column

[Value of Test] is the additional column that needs to be calculated
The dataset contain multiple articles [ID], and each article has several "transactions lines" ordered from oldest to newest
The value of this calculated column needs to be value in the column "Previous Value" of the first transaction after the transactions "START" as in the example below

Thx for the support and help 

Bjorn 

[amitchandak · Accepted solution]

@Bjorn_C , You can get previous value like

New column =
var _max = maxx(filter(Table, [ID] =earlier([ID]) && [Date] <earlier([Date])) , [Date])
return
maxx(filter(Table, [ID] =earlier([ID]) && [Date] =_max ) , [value])

But I am not clear on who you got those previous values

[v-yangliu-msft · Accepted solution]

Hi  @Bjorn_C ,

Here are the steps you can follow：

1. In Power query, Add Column – Index Column – From 1.

2. Create calculated column.

Value of test =
var _STARTIndex=CALCULATE(SUM('Table'[Index]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[rcode]="START"))
return
CALCULATE(SUM('Table'[Previous Value]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Index]=_STARTIndex+1))

3. Result:

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@Bjorn_C , You can get previous value like

New column =
var _max = maxx(filter(Table, [ID] =earlier([ID]) && [Date] <earlier([Date])) , [Date])
return
maxx(filter(Table, [ID] =earlier([ID]) && [Date] =_max ) , [value])

But I am not clear on who you got those previous values