Forum Discussion

kleien's avatar
kleien
Regular Visitor
6 years ago
Solved

DAX - Reference to previous value

Hi everyone! I need help with this. How can i create calculated column and within it reference to previous value in DAX I need this:  
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi kleien ,

    Create a calculated column like this:

    Result = 
CALCULATE(
    PRODUCT('Table'[Quantity]),
    FILTER(
        ALL('Table'),
        'Table'[Quantity] <= EARLIER('Table'[Quantity])
    )
)

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

kleien - First, you need to define what "previous" means. You can do this easily by creating an Index column in Power Query. Then you can use EARLIER coupled with MINX. See this article for an example: 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

 

Also, I see that you are relatively new. Refer to this article about common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

Welcome to the community.

kleien's avatar
kleien
Regular Visitor
6 years ago

Hi Greg!

Previous mean previous value of the same calculated column.

I did exactly what you said. I added Index and i used MINX and EAIRLER. But i cant reference to the previous value of the column Value (the column in which calculation happens).

 

So my question: Is any way to do it by DAX. I read somewhere that DAX operate at column level and cannot reference to previous calculated value of the column.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    kleien 

    lets consider row

    Quantity = 20, Value = 15000

    What is previous column for your business logic?

    I can suggest, you mean previous row it is a row with Quantity = 15. If so, why exactly 15 and not 5 and not 25 rows?

     

    In the most common case you can try a technique like

    Column = 
var _currentQuantity = [Quantity]
var _previousQuantity = CALCULATE(MAX('Table'[Quantity]), ALL('Table'), 'Table'[Quantity] < _currentQuantity)
var _previousValue = CALCULATE(MAX('Table'[Value]), ALL('Table'), 'Table'[Quantity] = _previousQuantity )
RETURN
_previousValue

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi kleien ,

     

    Create a Calculated Column

     

    All thanks to this post by Zubair_Muhammad 

     

    https://community.powerbi.com/t5/Desktop/get-value-from-Previous-row/td-p/441336

     

     

    Create a Ranking Column

    Ranking = RANKX('Table','Table'[Quantity],,ASC)

     

    Create another Calculated Column

     

     

    Calculated Column =
VAR PreviousRow =
    TOPN (
        'Table'[Ranking],
        FILTER (
            'Table',
            'Table'[Ranking]
                < EARLIER ( 'Table'[Ranking] )
        ),
        'Table'[Quantity], DESC
    )
VAR PreviousValue =
    PRODUCTX (
        PreviousRow,
        'Table'[Quantity]
    )
RETURN
    'Table'[Quantity] * PreviousValue

     

     

     

    Regards,
    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!