cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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