Forum Discussion
Dynamic Unit Price Lookup DAX Measure
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])--------------------------------------------------------------------------------------------------
So I have a Sales Table(Fact Table), Purchase Table(Fact Table) and SKU(Dim Table).
Relationships are as→
I want to calculate the Amount Column in the Sales table which is Sales[Qty]*Purchase[Rate].
But the catch is that Purchase Table have different rates for an SKU.
So based on the chronological order to calculate the Amount Col in the Sales table, I want to first look at the early transaction of an SKU in the Sales Table and then extract the early Purchase rate of that SKU from the Purchase Table and multiply it with Sales[Qty].
For earlier SKU transactions in the Sales table, I want to get the earlier
Rates From Purchase Table.
For Ex
Refer to SKU BT-01
All rates for BT-01 are arranged from the purchase table from oldest to newest date Purchase[Qty]*Purchase[Rate], let say BT-01_AllRates = (4 X 100, 6 X 120, 10 X 90)
For 1st BT-01 Transaction in Sales Table, Amount = Sales[Qty] * EarlyRate(BT-01_AllRates) =>2 X 100 = 200 and BT-01_AllRates updates to (2 X 100, 6 X 120, 10 X 90)
For 2nd BT-01 Transaction in Sales Table, Amount = Sales[Qty] * EarlyRate(BT-01_AllRates) =>2 X 100 + 1 X 120= 320 and BT-01_AllRates updates to (0 X 100, 5 X 120, 10 X 90)
I hope you get it now.
Hi asif999,
Let me try to rephrase what you said from what I understood:
1. BT-01 1st transaction - There is 2 quantity in the sales table and 4 quantity in the Purchase table. So 2 * 100 = 200
2. BT-01 2nd transaction - There is 3 quantity in the sale stable and 2 quantity in the Purchase table (after reducing 2 because of 1st transaction). So, 2 * 100 + 1 * 120
Is my understanding right?
Absolutely right
Hi asif999,
Does the date play a role in getting the price of SKUs? Like the sales transaction date should fall between the purchase dates from which we are using?
I think we can express this in DAX but it would be way too complex. Why not do it in Data engineering?
Yes, the date plays a role in getting the price for the SKUs.
In the sales table, it should pick up the SKU transaction in oldest to newest fashion and feed the rate from purchase table in the same fashion.
Yes, we can do it in data engineering but how?