Post reply

Return last purchase price per item DAX

  • April 27, 2022 at 10:07 pm

    #4023322

    In T-SQL this is stupid easy.

    use tempdb;
    go

    CREATE TABLE Purchases (
    PDate DATE,
    ProductID INT,
    Qty INT,
    Price SMALLMONEY);

    GO
    INSERT INTO Purchases VALUES ('3/1/2022',1,10,3.99),('3/15/2022',1,15,4.99),('3/1/2022',2,15,6.00),('3/15/2022',2,12,7.00);

    CREATE TABLE Product (ProductID INT PRIMARY KEY, ProductName VARCHAR(20));
    GO

    INSERT INTO Product(ProductID, ProductName) VALUES (1,'Widget'),(2,'Beer');

    SELECT p.ProductID, p.ProductName, LatestPrice = ca.Price
    FROM Product p
    CROSS APPLY (SELECT TOP 1 u.Price 
     FROM Purchases u
     WHERE u.ProductID = p.ProductID
     ORDER BY u.PDate DESC) ca;

    I can get the latest purchase date for each item from a SalesFact table (ProductID, SaleDate, Qty, Price...) by using SUMMARIZE('FactTable','ProductID',"LastSaleDate", LASTDATE([SaleDate])) but how do I get the price from that, since it requires a double join? Do I have to concatenate ProductID and LastSaleDate and join that to Sales? <shudder>

  • April 28, 2022 at 11:14 am

    #4023588

    The pattern to do that is similar to inventory snapshots, where you'd like to return the last record of each product/item. More info on how to do it here: https://www.sqlbi.com/articles/inventory-in-power-pivot-and-dax-snapshot-vs-dynamic-calculation/

  • April 28, 2022 at 2:44 pm

    #4023729

    Thanks Martin!, I'll have a look.

    And I guess I have to dust off Ralph Kimball's book.

  • April 28, 2022 at 2:46 pm

    #4023730

    No worries, and only if you really want to 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply