Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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