Skip to main content
cancel
Showing results for 
Search instead 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

Reply
paulj1
Helper II
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.

 

paulj1_0-1594821472058.png

What I then have is a reference table  with a Market price for each Product and Source region on a date.

 

paulj1_1-1594821559520.png

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
jdbuchanan71
Super User
Super User

@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]

 

jdbuchanan71_0-1594826303260.png

I have attached my sample file for you to look at.

 

View solution in original post

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

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

 jdbuchanan71_0-1595613857266.png

 

View solution in original post

8 REPLIES 8
jdbuchanan71
Super User
Super User

@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]

 

jdbuchanan71_0-1594826303260.png

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

paulj1_0-1595414958932.png

Purchase Date

paulj1_1-1595414981561.png

 

Purchase Qty & Price

paulj1_2-1595415026016.png

 

Supplier Table

paulj1_3-1595415053693.png

Model

paulj1_4-1595415077652.png

 

 

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

 

paulj1_1-1595595251178.png

 

This is the data model

 

paulj1_0-1595595034211.png

 

Sample file here..

 

https://1drv.ms/u/s!ApcyZzyFx2-9hZBD2RmW3TFZbpBBFQ?e=GgR9D5

 

Thanks for any help... appreciated...

 

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

jdbuchanan71_0-1595605700639.png

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.

jdbuchanan71_1-1595605857958.png

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

 

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

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

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

 jdbuchanan71_0-1595613857266.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors