Problem with DAX query

[davidrgh]

Hi

I'm trying to do solve the next problem and I'm not able.

I've the next table in my BBDD:

Date       Qty       Prev_week

Date = Day of the record.

Qty = Integer that refers the units of a product

Prev_week = Last day of the previous week (considering Date field).

I want to do a calculated column that shows the Qty value of the record with the Date is equal to the Prev_week of the current record. For example:

Date                 Qty              Prev_week                Prev_Qty

20/01/2018       3                 14/01/2018               0

21/01/2018       5                 14/01/2018               0

22/01/2018       4                 21/01/2018               5

I've tried everything that ocurred to me, but I didn't get successful

Thank's in advance

[v-xjiin-msft · Accepted solution]

@davidrgh

In your scenario, did you mean that the logic to calculate the Prev_Qty is if the current Prev_week equals to Date in prior row, then the Prev_Qty equals to the Qty in prior row?

If so, to achieve your requirement you can refer to following steps:

1. Create an index column for your source table to make a sequence. Go to Edit Queries => Add column => Index Column
2. Use Lookupvalue() function to get prior value. The expression is like:

Prev_Qty =
IF (
    LOOKUPVALUE ( Table4[Date], Table4[Index], Table4[Index] - 1 )
        = Table4[Prev_week],
    LOOKUPVALUE ( Table4[Qty], Table4[Index], Table4[Index] - 1 ),
    0
)

Thanks,
Xi Jin.