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.

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!