Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |