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.

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
Icon for Community Champion rankCommunity 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