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 un...
  • 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.

v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution 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.