'How to get previous row value of a text column in new column in Dax -Power BI

In PowerBI, I'm trying to get a calculated column (not a measure) that gets preivous row value. I tried adding a column with DAX formula as below:

DAX Index = RANKX(ALL( InventoryItems ),InventoryItems[INVENTORYDATE],,ASC,Dense)

STATUSyesterday = VAR _CurrentRowIndex = InventoryItems[DAX Index] VAR _PreviousRowIndex = CALCULATE(MAX( InventoryItems[DAX Index] ),FILTER( InventoryItems, InventoryItems[DAX Index] <_CurrentRowIndex ),ALL( InventoryItems[DAX Index] ))VAR _Result = CALCULATE(MAX(InventoryItems[STATUSinventory] ),FILTER( InventoryItems, InventoryItems[DAX Index]=_PreviousRowIndex ))RETURN _Result

Ideally, result of STATUSyesterday should first row blank and from second row onwards it should be STATUSinventory instead, its just 'out of stock' as it is occurring maximum time.

query image

powerbi dax

Solution 1:^[1]

Try this and let me know if it works. What I am doing is getting the id for the previous row and the matching the "STATUSinventory" with the previous id.

Previous Row Value =
VAR _PreviousRow = 
CALCULATE (
    MAX ( 'InventoryItems'[DAX Index] ),
    ALL ( 'InventoryItems' ),
    'InventoryItems'[ITEMNUMBER]
        = EARLIER ( 'InventoryItems'[ITEMNUMBER] ),
    'Summary Table'[Date] < EARLIER ( 'InventoryItems'[INVENTORYDATE] )
)

RETURN 

CALCULATE (
LASTNONBLANK ( 'InventoryItems'[STATUSinventory], 1 ),
ALL ('InventoryItems' ),
'InventoryItems'[DAX Index] = _PreviousRow
)

Solution 2:^[2]

It is not necessary to add an index. You can use the Inventorydate to determine the order. Try something like this:

STATUSyesterday =
VAR currentInventoryDate = 'InventoryItems'[INVENTORYDATE]
VAR previousInventoryDate =
    CALCULATE (
        MAX ( 'InventoryItems'[INVENTORYDATE] ),
        ALL ( 'InventoryItems' ),
        'InventoryItems'[INVENTORYDATE] < currentInventoryDate
    )
RETURN
    CALCULATE (
        MAX ( 'InventoryItems'[STATUSinventory] ),
        ALL ( 'InventoryItems' ),
        'InventoryItems'[INVENTORYDATE] = previousInventoryDate
    )

[currentInventoryDate] is the Inventorydate in the current row.

[previousInventoryDate] is the max(IventoryDate) that is smaller than the [currentInventoryDate].

The last CALCULATE returns the STATUSinventory of the previousInventoryDate.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Marco Vos

'How to get previous row value of a text column in new column in Dax -Power BI

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]