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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
mellon199
Frequent Visitor

Use previous row in current column calculation

Hello, trying to create a calculated column using data from the previous row.

 

See example below for situation which has been calculated in excel for Indexation Factor.

 

Indexation factor formula = B2*(1+A3)

Within excel row B1 is manually set to 1 and the fields below are using the calculation above.

Can this be replicated using a calculated column within PowerBI? Thanks in advance.

 

(A) Selected Index(B) Indexation Factor
 1
0.0296831.029683334
0.0045781.03439749
0.0496291.08573384
0.0479821.137829244
0.0308981.172985656
0.0288481.206823699
0.0195981.230475107
0.0107791.24373867
0.0214251.270385647
0.0374231.31792673
0.0305661.358210528
0.0258741.393353256
0.0121281.410252314
0.0577621.491711364
0.1232621.675582498
0.0430631.747738101
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @mellon199 
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAi

1.png

2.png

For a caclulated column:

 

Indexation Factor = 
VAR T1 = 
    FILTER ( 
        Data, 
        Data[Index] <= EARLIER ( Data[Index] ) 
    )
RETURN
    PRODUCTX ( 
        T1,
        1 + Data[Selected Index]
    )

 

For a measure:

 

Indexation Factor Measure = 
VAR SelectedIndex = 
    SELECTEDVALUE ( Data[Index] )
VAR T1 = 
    CALCULATETABLE ( 
        Data, 
        Data[Index] <= SelectedIndex,
        ALL ( Data[Selected Index] )
    )
RETURN
    PRODUCTX ( 
        T1,
        1 + Data[Selected Index]
    )

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @mellon199 
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAi

1.png

2.png

For a caclulated column:

 

Indexation Factor = 
VAR T1 = 
    FILTER ( 
        Data, 
        Data[Index] <= EARLIER ( Data[Index] ) 
    )
RETURN
    PRODUCTX ( 
        T1,
        1 + Data[Selected Index]
    )

 

For a measure:

 

Indexation Factor Measure = 
VAR SelectedIndex = 
    SELECTEDVALUE ( Data[Index] )
VAR T1 = 
    CALCULATETABLE ( 
        Data, 
        Data[Index] <= SelectedIndex,
        ALL ( Data[Selected Index] )
    )
RETURN
    PRODUCTX ( 
        T1,
        1 + Data[Selected Index]
    )

 

Amazing @tamerj1 - Thank you for taking the time to complete a working example!  😊

Greg_Deckler
Community Champion
Community Champion

@mellon199 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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