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.

5 Replies

  • 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

         

  • v-yingjl's avatar
    v-yingjl
    Community Support
    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.