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
Abevann
Helper III
Helper III

Do formulas with values of previous rows

Guys,

I need to get a value from a previous row, but I'm having troubles in order to do so. An example, This is the table that I'm looking for:

Date            Fruit      Buy Sold Stock         Buy - Sold = Stock

1/8/2021     Lime     90     70     20            90 - 70 = 20

5/8/2021     Orange 60     50    10             60 - 50 = 10

7/8/2021     Lime     70     80    10             90 (70 + 20) - 80 = 10

At the end of August 7th, there are 10 limes and 10 oranges in stock. I tried with LOOKUPVALUE and some others, but with no success so far. 

Any idea how to solve this? Thanks!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Abevann ,

You can create a measure or calculated column as below to achieve it, please find the details in the attachment.

1. Create a measure

Stock in previous row = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _curdate )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Stock] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _predate )
    )

2. Create a calculatd column

Column = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (   'Table' , 'Table'[Date] < EARLIER('Table'[Date]) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Stock] ),
        FILTER ( 'Table' , 'Table'[Date] = _predate )
    )

yingyinr_0-1630034148990.png

In addtion, you can also refer the method in the following links to achieve it.

Get previous row in Power Query - EXPLAINED

get value from Previous row

Calculate previous row using DAX

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

Hi @Abevann ,

You can create a measure or calculated column as below to achieve it, please find the details in the attachment.

1. Create a measure

Stock in previous row = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _curdate )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Stock] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _predate )
    )

2. Create a calculatd column

Column = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (   'Table' , 'Table'[Date] < EARLIER('Table'[Date]) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Stock] ),
        FILTER ( 'Table' , 'Table'[Date] = _predate )
    )

yingyinr_0-1630034148990.png

In addtion, you can also refer the method in the following links to achieve it.

Get previous row in Power Query - EXPLAINED

get value from Previous row

Calculate previous row using DAX

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yiruan-msft !

Greg_Deckler
Super User
Super User

@Abevann Looks like a variation on MTBF: 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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler ,

Tha solution works, but I had the problem that the information if from different tables. I'm struggling with this, but it does what I was looking for.

 

Thanks, regards,

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.