Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Help.... can do a multi faceted lookup, and cant find a article that gives me the answer....
So, I have purchases of products, by date and by a source region, and price paid, simple enough.
What I then have is a reference table with a Market price for each Product and Source region on a date.
What I want to do is return the Market price for each purchase, so looking at first purchase; Product A from UK on 10th June; this needs to return the 31st May Market price for Product A from UK... Cant seem to get my DAX right to return it... From this for each purchase I can calculate difference to know if purchase was below or above, or at Market Value.
I'd like to avoid calculated column if it can, and do it in a DAX statement....
Note always return Market price for nearest date in past, Market Price date is not always at end of month, as i may have mid month prices in some months.
Solved! Go to Solution.
I got something working but in order to get the context transitions to behave when calculating the delta I had to do it in a couple steps.
Measure to sum the purchase price.
Related Market Price Sum = SUMX ( Purchases, [Related Market Price] )
Now the retrieval of the related market price.
Related Market Price =
VAR _PurchaseDate = SELECTEDVALUE ( Purchases[Purchase Date] )
VAR _PriceDate =
CALCULATE(
MAX(Reference[Date]),
TREATAS(
SELECTCOLUMNS(
Purchases,
"Product",Purchases[Product],
"Source",Purchases[Source]),
Reference[Product],
Reference[Region]
),
Reference[Date] < _PurchaseDate
)
RETURN
CALCULATE(
SUM ( Reference[Market Price] ),
TREATAS(
SELECTCOLUMNS(
Purchases,
"Product",Purchases[Product],
"Source",Purchases[Source]),
Reference[Product],
Reference[Region]
),
Reference[Date] = _PriceDate
)
Then, in order to get totals working I use the above in a SUMX over the purchases table.
Related Market Price Sum = SUMX ( Purchases, [Related Market Price] )
And finally the delta calc
Delta = [Purchase Price] - [Related Market Price Sum]
I have attached my sample file for you to look at.
You can invoke that relationship against your measure like you mentioned.
Delta Order Date = CALCULATE([Delta],USERELATIONSHIP('Calendar'[Date],vDeliveries[orderdate]))
I got something working but in order to get the context transitions to behave when calculating the delta I had to do it in a couple steps.
Measure to sum the purchase price.
Related Market Price Sum = SUMX ( Purchases, [Related Market Price] )
Now the retrieval of the related market price.
Related Market Price =
VAR _PurchaseDate = SELECTEDVALUE ( Purchases[Purchase Date] )
VAR _PriceDate =
CALCULATE(
MAX(Reference[Date]),
TREATAS(
SELECTCOLUMNS(
Purchases,
"Product",Purchases[Product],
"Source",Purchases[Source]),
Reference[Product],
Reference[Region]
),
Reference[Date] < _PurchaseDate
)
RETURN
CALCULATE(
SUM ( Reference[Market Price] ),
TREATAS(
SELECTCOLUMNS(
Purchases,
"Product",Purchases[Product],
"Source",Purchases[Source]),
Reference[Product],
Reference[Region]
),
Reference[Date] = _PriceDate
)
Then, in order to get totals working I use the above in a SUMX over the purchases table.
Related Market Price Sum = SUMX ( Purchases, [Related Market Price] )
And finally the delta calc
Delta = [Purchase Price] - [Related Market Price Sum]
I have attached my sample file for you to look at.
Yes the solution worked... however..when i went to use it in anger, my model is more complicated that I initally showed... cant make it now work... help !...
Market price
Purchase Date
Purchase Qty & Price
Supplier Table
Model
The output I need remains the same.. for each delivery (packno) price paid vs Market price (most recent date in the past for that product from that region)
Thanks in advance...
@paulj1
Please share a copy of your .pbix file. You can load it to OneDrive or DropBox and post the link here.
https://1drv.ms/u/s!ApcyZzyFx2-9hZBD2RmW3TFZbpBBFQ?e=GgR9D5
Thanks... managed to answer my question ref further tables by adding two more variables in the DAX to lookupvalue in the original code, so thankyou, that works, maybe a more efficient DAX than LOOKUPVALUE though.
VAR _SupplierRegion =CALCULATE(LOOKUPVALUE( 'eMarkets'[Region] , 'eMarkets'[Supplier] , _Supplier ))VAR _Product =CALCULATE(LOOKUPVALUE( 'vDeliveries - Unpivot'[Prodcat] , 'vDeliveries - Unpivot'[packno] , _PackNo ))
I now have a time intelligence problem, how do i insert a USERELATIONSHIP( 'Calendar'[Date] , vDeliveries[orderdate] ) in the DAX, because i need to use the Calendar table to filter, but the primary date relation ship is not with Orderdate.
Look below I am filter in by February but have a january delivery shown, only want to show February deliveries...
This is the data model
Sample file here..
https://1drv.ms/u/s!ApcyZzyFx2-9hZBD2RmW3TFZbpBBFQ?e=GgR9D5
Thanks for any help... appreciated...
I would start with some cleanup on the model. It looks like you can have a single Delivery table that has all the fields.
Right now your 'vDeliveries - Unpivot' table has 19,320 blank rows.
Your vDelivers to vIntoWork is a 1:1 which means the [Date Into Work] can just be moved to the Deliveries table. This will let you have a more straight forward model to work with.
Which makes writing the measures simpler.
Ah... not that simple. I made up an sample file to anonomyse the data and i made the intoWork table too simple.
In reality all deliveries arent totally used, eg a delivery of 100, only 80 might be put into work, 20 remain in stock; also using them maybe split over a number of days eg a delivery of 100, 50 might be used 1 day, 20 the next, 30 three days after that...
I have made the model more representative... Apols....
You can invoke that relationship against your measure like you mentioned.
Delta Order Date = CALCULATE([Delta],USERELATIONSHIP('Calendar'[Date],vDeliveries[orderdate]))
Thank you so much... i'll go and have a look over....
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |