DAX - Reference to previous value

[kleien]

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:

[v-yingjl · Accepted solution]

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]

@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/3395...

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]

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.

[harshnathani]

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!

[az38]

@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