Forum Discussion

davidrgh's avatar
davidrgh
New Member
8 years ago
Solved

Problem with DAX query

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's avatar
    v-xjiin-msft
    8 years ago

    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.

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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.