Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
davidrgh
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

 

Thank's in advance

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
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
)

111.PNG

 

Thanks,
Xi Jin.

View solution in original post

1 REPLY 1
v-xjiin-msft
Solution Sage
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
)

111.PNG

 

Thanks,
Xi Jin.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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