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.
Hi,
I am trying to replicate the following calculation in DAX, where I want to get the value from the previous row in the current column (B).
Solved! Go to Solution.
Hi @Pinecone
If B is a calculated column then
B =
SUMX (
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
'Table'[A]
)
Hi @Pinecone
Please refer to attached sample file with the proposed solution
B =
COALESCE (
DIVIDE (
1,
PRODUCTX (
FILTER (
'Table',
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Index] > 1
),
( 1 + 'Table'[A] ) ^ (1/16)
)
),
1
)
The numbers do not 100% match yours, but when I made the my own excel sample file, the numbers were 100% matching. Could it be a rounding error?
Value =
COALESCE (
PRODUCTX (
FILTER (
Table2,
Table2[Index] <= EARLIER ( Table2[Index] )
&& Table2[Index] > 1
),
1 + Table2[A]
) * 100,
100
)
I have another similar issue.
Initial value in column C (C1) = 100
The following values in column C is: C_i (C_i where i > 1) = C_i-1 *(1+A_i)
Value =
COALESCE (
PRODUCTX (
FILTER (
Table2,
Table2[Index] <= EARLIER ( Table2[Index] )
&& Table2[Index] > 1
),
1 + Table2[A]
) * 100,
100
)
Hi @Pinecone
If B is a calculated column then
B =
SUMX (
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
'Table'[A]
)
That did work fine. What if I want to expand the complexity by the following, knowing that B1 = 1,
Hi @Pinecone
Please refer to attached sample file with the proposed solution
B =
COALESCE (
DIVIDE (
1,
PRODUCTX (
FILTER (
'Table',
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Index] > 1
),
( 1 + 'Table'[A] ) ^ (1/16)
)
),
1
)
The numbers do not 100% match yours, but when I made the my own excel sample file, the numbers were 100% matching. Could it be a rounding error?
Amazing, and thank you so much for solving my issue! ❤️
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |