cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## 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

1 ACCEPTED SOLUTION
Solution Sage

@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.

Solution Sage

@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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors