March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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....
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
93 | |
72 | |
58 |