Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pinecone
Frequent Visitor

How to get value from previous row in current calculated column

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). 

 Example.PNG

3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @Pinecone 
If B is a calculated column then

B =
SUMX (
    FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
    'Table'[A]
)

View solution in original post

Hi @Pinecone 
Please refer to attached sample file with the proposed solution

1.png

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?

View solution in original post

@Pinecone 
1.png

Value = 
COALESCE (
    PRODUCTX (
        FILTER ( 
            Table2,
            Table2[Index] <= EARLIER ( Table2[Index] )
                && Table2[Index] > 1 
        ),
        1 + Table2[A]
    ) * 100,
    100
)

View solution in original post

7 REPLIES 7
Pinecone
Frequent Visitor

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)

PowerBI_example.PNG

@Pinecone 
1.png

Value = 
COALESCE (
    PRODUCTX (
        FILTER ( 
            Table2,
            Table2[Index] <= EARLIER ( Table2[Index] )
                && Table2[Index] > 1 
        ),
        1 + Table2[A]
    ) * 100,
    100
)
tamerj1
Super User
Super User

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, Example2.PNG

Hi @Pinecone 
Please refer to attached sample file with the proposed solution

1.png

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! ❤️

@Pinecone 

Ya that is pretty complex. I'll have a look at it tomorrow. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors