Forum Discussion

asif999's avatar
asif999
Frequent Visitor
2 years ago
Solved

Dynamic Unit Price Lookup DAX Measure

Google Sheet Link 1. I want to calculate the Amount dax measure as shown in Sales[Amount] col. 2. For the earliest transaction in the Sales Table, I want to look for the earliest SKU rate from the ...
DAX
  • talespin's avatar
    talespin
    2 years ago

    hi asif999 

     

    Please see if this work for you. It involves joins so will be slow if there are too many average# of records for each SKU. 

     

    Note : My advise is to do it in Power Query, it is very easy to implement in Power Query.

    Assumption : SKU and OrderID combination is unique

     

    Created Sales and Purchase table(Only two tables without any relationship between them)

     

    Please create this measure, it is for Sales Amount only.

    --------------------------------------------------------------------------------------------------

    SalesAmount =
    VAR _SKU = SELECTEDVALUE(Sales[SKU])
    VAR _SalesOrderID = SELECTEDVALUE(Sales[OrderID])
    VAR _SummTbl =
    GENERATE (
        FILTER(ALL(Sales),[SKU] = _SKU),
        GENERATESERIES ( 1, Sales[Qty] )
    )
    VAR _AddRankSales =
    SELECTCOLUMNS(
                    ADDCOLUMNS(_SummTbl, "@JoinColumn", RANKX(_SummTbl, (([OrderID]*10)+[Value]),,ASC, Dense ) ),
                    "@SKUSales", [SKU],
                    "@OrderIDSales", [OrderID],
                    "@JoinColumn", [@JoinColumn]
    )
    VAR _SummTbl2 =
    GENERATE (
        FILTER(ALL(Purchase),[SKU] = _SKU),
        GENERATESERIES ( 1, Purchase[Qty] )
    )
    VAR _AddRankPurchase = ADDCOLUMNS(_SummTbl2, "@JoinColumn", RANKX(_SummTbl2, INT([Date])+(([Qty]*10)+[Value]),,ASC, Dense ) )
    VAR _JoinSalesPurchase = NATURALINNERJOIN(_AddRankSales, _AddRankPurchase)
    VAR _SummResult = ADDCOLUMNS(
                                    SUMMARIZE(_JoinSalesPurchase, [@SKUSales], [@OrderIDSales]),
                                    "@SalesAmount",
                                    VAR _OrderID = [@OrderIDSales]
                                    RETURN SUMX(FILTER(_JoinSalesPurchase, [@OrderIDSales] = _OrderID),[Rate])
                            )
    RETURN SELECTCOLUMNS( FILTER(_SummResult, [@SKUSales] = _SKU && [@OrderIDSales] = _SalesOrderID), "@SalesAmount", [@SalesAmount])

    --------------------------------------------------------------------------------------------------

     

     

     

govindarajan_d's avatar
govindarajan_d
Super User
2 years ago

Hi asif999,

 

In Data engineering, we normally calculate the difference in SKUs and store it in separate table as part of processing. And then we use that table for calculating the current transaction. Usually we use the LAG, LEAD function in SQL. 

 

In terms of DAX, I believe we have a way to do this by using the order date. 

 

Give me some time, I will try to work out a DAX formula. 

asif999's avatar
asif999
Frequent Visitor
2 years ago

Hi govindarajan_d, just wanted to drop a quick thank you for your assistance. Looking forward to your response – much appreciated!

  • govindarajan_d's avatar
    govindarajan_d
    Super User
    2 years ago

    Hi asif999,

     

    I tried to work on it over the weekend. One of the major problems that I couldn't overcome was the stock distribution across different rows. Usually we can aggregate the rows and use it for calculation, but in this case it is a calculation that is split across different rows. 

     

    I am trying different formulas, but I just have an intermediate experience working with DAX. You can try tagging some of the experts to see if they can solve this!