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

[paulj1]

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.  

[jdbuchanan71 · Accepted solution]

@paulj1 

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.

[jdbuchanan71 · Accepted solution]

@paulj1 

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.

[paulj1]

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

[jdbuchanan71]

@paulj1 
Please share a copy of your .pbix file.  You can load it to OneDrive or DropBox and post the link here.

[paulj1]

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

[jdbuchanan71]

@paulj1 

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.

[paulj1]

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

[jdbuchanan71 · Accepted solution]

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

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

 

[paulj1]

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