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 All,
I have a table with various item and their value for dates. I have calculated the change of their value as return. Now I need to create a column that refers to its previous value of the same column. My data is as follows:
Date | Type | Value | Return | Column |
1/1/2020 | A | 1 | 100 | |
1/2/2020 | A | 2 | 1 | 200 |
1/3/2020 | A | 3 | 0.5 | 101 |
1/4/2020 | A | 4 | 0.333333 | 134.6667 |
1/5/2020 | A | 5 | 0.25 | 102 |
1/1/2020 | B | 7 | 100 | |
1/2/2020 | B | 8 | 0.142857 | 103 |
1/3/2020 | B | 9 | 0.125 | 115.875 |
1/4/2020 | B | 10 | 0.111111 | 104 |
1/5/2020 | B | 11 | 0.1 | 114.4 |
I need to calculate the new column whose formula is:
if(return = 0), then 100
else previous value * (1+ return)
It is quite staright forward in excel. We can directly do, E4 = E3*(1+D4)
When i create a similar formula in DAX, i am getting a circular loop error. Any helps is appreciated.
Solved! Go to Solution.
Hi @sonm10 ,
Please check if this is what you want:
Column 2 =
VAR CurrentDate = [Date]
VAR t =
ADDCOLUMNS ( 'Table', "Return_1", [Return] + 1 )
RETURN
PRODUCTX (
FILTER ( t, [Type] = EARLIER ( [Type] ) && [Date] <= CurrentDate ),
[Return_1]
) * 100
But I have some confusion. Is the data marked above written wrong?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sonm10 ,
Please check if this is what you want:
Column 2 =
VAR CurrentDate = [Date]
VAR t =
ADDCOLUMNS ( 'Table', "Return_1", [Return] + 1 )
RETURN
PRODUCTX (
FILTER ( t, [Type] = EARLIER ( [Type] ) && [Date] <= CurrentDate ),
[Return_1]
) * 100
But I have some confusion. Is the data marked above written wrong?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot @Icey . Exactly what i wanted. I had made some mistakes in the sample data and yet you provided exactly what i wanted.
@sonm10 , try a new column
if(coalesce([Return],0) =0,100 , [value] * (1+ [return]))
if(([Return]+0) =0,100 , [value] * (1+ [return]))
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |