cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Measure DAX to lookup value by nearest date in past and categories

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.

2 ACCEPTED SOLUTIONS
Super User

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.

Super User

You can invoke that relationship against your measure like you mentioned.

``Delta Order Date = CALCULATE([Delta],USERELATIONSHIP('Calendar'[Date],vDeliveries[orderdate]))``

8 REPLIES 8
Super User

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.

Helper II

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)

Super User

@paulj1

Helper II

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...

Super User

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.

Helper II

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....

https://1drv.ms/u/s!ApcyZzyFx2-9hZBEmQ1DKwRf-eseeA?e=b2tfrD

Super User

You can invoke that relationship against your measure like you mentioned.

``Delta Order Date = CALCULATE([Delta],USERELATIONSHIP('Calendar'[Date],vDeliveries[orderdate]))``

Helper II

Thank you so much... i'll go and have a look over....