Forum Discussion

Abevann's avatar
Abevann
Icon for Helper III rankHelper III
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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/339586.
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

  • Abevann's avatar
    Abevann
    Icon for Helper III rankHelper III
    5 years ago

    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,