CODEWITHSUNDEEP
powerbi
kumarb
kumarb

Reputation: 49

How to get previous row value in DAX power bi

family  month  qty  prev_qty

A        M07   5     5
A        M08   6     5
A        M09   7     6
B        M07   10    10
B        M08   11    10
B        M09   12    11

Previous qty is the column i want to populate in my dashboard. I used dax like earlier and also created index but its not helping. Any DAX function for this table to find the previous row value?

Upvotes: 3

Views: 15048

Answers (1)

Peter
Peter

Reputation: 12325

Bad question. It seems what you are actually looking for is this:

For each family sort the table by month, and either take the quantity from the preceeding month or the same month if there is no predecessor.

This can be achieved using three calulated columns:

Current Row = RANKX( ALL('Table'), 'Table'[month], , ASC, Dense)

Previous Row = 
VAR CurrentRow = 'Table'[Current Row]
VAR PreviousRow = CALCULATE(
    MAX('Table'[Current Row]), 
    ALL('Table'),
    'Table'[family] = EARLIER('Table'[family]),
    'Table'[month] < EARLIER('Table'[month])
)
RETURN IF( PreviousRow <> BLANK(), PreviousRow, CurrentRow )

Previous Quantity = 
CALCULATE(
    MAX('Table'[qty]), 
    ALL('Table'), 
    'Table'[family] = EARLIER('Table'[family]), 
    'Table'[Current Row] = EARLIER('Table'[Previous Row])
)

enter image description here

Please accept the answer if this is what you're looking for and reward voluntary help.

Upvotes: 4

Related Questions