Get Sum of quantity in related table based on Max of date

[mblom]

Hi!

I am new to power bi and I am stuck with the following requirement:

I have 3 tables:

Product: productid, description

Purchase: purchaseid, purchasedate

PurchaseProduct: purchaseid, productid, quantity

In my table visual I would like to list all products with their id and description. Furthermore, I would like to show the SUM of Quantity (SUM of PurchaseProduct.Quantity) for the most recent Purchasedate (MAX of purchasedate).

I have been trying to summarize these values with M while getting the data from the sources but this failed. I also feel that I should be able to get this calculation using DAX.

Sample data for Product:

ProductId	Description
1	Tape
2	Mainboard
3	LCD
4	Screw
5	Backcover

Sample data for Purchase:

PurchaseId	Purchasedate
1	2021-01-10
2	2021-01-12
3	2021-01-14
4	2021-02-01

Sample data for PurchaseProduct:

Purchaseid	Productid	Quantity
1	1	5
1	2	2
1	4	6
2	1	10
2	2	20
3	3	5
3	4	7
4	1	10
4	5	8
4	1	20

The expected result would be a table like:

Productid	Description	Last purchasedate	Last total quantity
1	Tape	2021-02-01	30
2	Mainboard	2021-01-12	20
3	LCD	2021-01-14	5
4	Screw	2021-01-14	7
5	Backcover	2021-02-01	8

Any help would be appreciated!

Mark

[mahoneypat · Accepted solution]

I assume your model has 1:M relationships on Product ID and Purchase ID (and that your PurchaseDate column is type Date).  You should consider adding a Date table to your model.  You can use a measure expression like this in your table visual with Product ID and Description.

Latest Purchase Amt =
LASTNONBLANKVALUE (
    Purchase[PurchaseDate],
    CALCULATE (
        SUM ( PurchaseProduct[Quantity] )
    )
)

If interested, hear is an article/video on adding a Date table.

Creating a simple date table in DAX - SQLBI

Pat

[mblom]

Thanks, that works like charm!

[timg]

Hi mblom,

you could use this method.

Step 1 is making a calculated column in the Purchaseproduct table with the related Purchase date for eacht order

PurchaseDate = RELATED(Purchase[Purchasedate])

 Step 2 is making a measure in which only the quantity related to the max date is summed up:

MaxProductdateQuantity = 
CALCULATE (
    SUM ( PurchaseProduct[Quantity] ),
    FILTER (
        PurchaseProduct,
        PurchaseProduct[PurchaseDate] = MAX ( PurchaseProduct[PurchaseDate] )
    )
)

Lastly, you can place the calculated column and measure in a table to show the desired result (set the calculated column to "latest" to show the last date per productid).

Hope that helps!

Regards,

Tim

[mblom]

Looks like a viable solution as well! I will try this tomorrow. Thanks for your suggestion!