Forum Discussion
A calculated column returning a specific value from an "internal table"
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
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
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
2 Replies
- amitchandak
Super User
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
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