cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

DAX measure to get latest stock qty for products by datetime and eventid

Hi,

I'm trying to get latest stock quantity for every ITEMID by WAREHOUSE and LOCATION from inventory events table based on two columns. I need to return the row QTY with latest EVENTDATETIME and if duplicate timestamps exist then the row with highest EVENTID. Eventid alone cannot be used since new event has always the highest id and events might have been created manually afterwards with manually inserted timestamps (ie user might create an event for year 2015 in 2020).

 

Example of data for one product, I want to return qty of the green rows which have highest eventid:

ITEMIDEVENTIDWAREHOUSELOCATIONEVENTDATETIMEQTY
842039931124WH1L111.1.2005 0:0037504,24
8420392303041WH1L114.1.2005 9:1137600
8420392282976WH1L114.1.2005 9:1147600
8420392302640WH1L1213.1.2005 12:4027504,24
8420392303078WH1L1213.1.2005 15:2227504,24
8420392303079WH1L1213.1.2005 15:22123269,2
8420392303048WH1L1213.1.2005 15:22123269,2
8420392309163WH2L2214.12.2011 13:59122269,2
8420392325883WH2L2214.12.2011 13:59117269,2
8420392349151WH2L2214.12.2011 13:59113269,2
8420392392999WH2L2214.12.2011 14:01112269,2
8420392397209WH2L2214.12.2011 14:01109269,2

 

Resulting table visual in PBI should show latest qty for item, its warehouse and location, their sum on the total row:

ItemWarehouseLocationStock Qty
842039WH1L1137600
842039WH1L11123269,2
842039WH2L22112269,2
843033WH5L51450
844000WH5L52100
TOTAL  273688,4

 

 

 

What I've managed thus far is to return values only based on either the datetime or eventid.

This works fine to return qty by the eventdatetime only but I'm really struggling to modify it to also account for the eventid:

 

 

 

Stock QTY=
SUMX(
    VALUES(INVENTORYEVENTS[WAREHOUSE]);
    SUMX(
        VALUES(INVENTORYEVENTS[LOCATION]);
        SUMX (
            VALUES ( INVENTORYEVENTS[ITEMID] );
            CALCULATE (
                SUM(INVENTORYEVENTS[QTY]);
                LASTNONBLANK(
                    INVENTORYEVENTS[EVENTDATETIME];
                    CALCULATE ( SUM(INVENTORYEVENTS[QTY]) )
                )
            )
        )
    )
)

 

 

 

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

The only way that seems to work is to add another table with calculated columns. I'm not sure how this will work when scaled up but it does produce the desired results.

 

Let me know if it works for you. Thanks

 

The calculated columns are

MaxDate =
VAR ItemID = ItemLocations[ITEMID]
VAR Warehouse = ItemLocations[WAREHOUSE]
VAR Locations = ItemLocations[LOCATION]
VAR MaxDate =
CALCULATE (MAX ( InventoryEvents[EVENTDATETIME] ),
FILTER ( InventoryEvents, InventoryEvents[ITEMID] = ItemID && InventoryEvents[WAREHOUSE] = Warehouse
&& InventoryEvents[LOCATION] = Locations) )
RETURN
MaxDate


MaxEvent =
VAR ItemID = ItemLocations[ITEMID]
VAR Warehouse = ItemLocations[WAREHOUSE]
VAR Locations = ItemLocations[LOCATION]
VAR MaxEvent =
CALCULATE (MAX ( InventoryEvents[EVENTID] ),
FILTER ( InventoryEvents, InventoryEvents[ITEMID] = ItemID && InventoryEvents[WAREHOUSE] = Warehouse
&& InventoryEvents[LOCATION] = Locations) )
RETURN
MaxEvent


StockQty =
CALCULATE(
[TotalQty],
FILTER(RELATEDTABLE(InventoryEvents), InventoryEvents[ITEMID] = ItemLocations[ITEMID]
&& InventoryEvents[EVENTDATETIME] = ItemLocations[MaxDate]
&& InventoryEvents[EVENTID] = ItemLocations[MaxEvent]
&& InventoryEvents[WAREHOUSE] = ItemLocations[WAREHOUSE]
&& InventoryEvents[LOCATION] = ItemLocations[LOCATION] )
)

 

INVENTORY EVENTS.jpg

 

View solution in original post

11 REPLIES 11
Community Support
Community Support

Hi @Juksho ,

 

Try this:

Stock Qty =
VAR EventDateTime_ =
    CALCULATE (
        MAX ( INVENTORYEVENTS[EVENTDATETIME] ),
        ALLEXCEPT (
            INVENTORYEVENTS,
            INVENTORYEVENTS[WAREHOUSE],
            INVENTORYEVENTS[LOCATION]
        )
    )
VAR EventId_ =
    CALCULATE (
        MAX ( INVENTORYEVENTS[EVENTID] ),
        INVENTORYEVENTS[EVENTDATETIME] = EventDateTime_
    )
RETURN
    CALCULATE (
        SUM ( INVENTORYEVENTS[QTY] ),
        INVENTORYEVENTS[EVENTID] = EventId_,
        INVENTORYEVENTS[EVENTDATETIME] = EventDateTime_
    )

QTY.PNG

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks @Icey 

Your measure work great for returning the correct values for each product, warehouse and location.

But the Total for the products combined is not working since it's returning only quantity of a single product that has the latest event.

 

Resolver I
Resolver I

A question - why would the EVENTID alone not be sufficient to identify the last transaction for an item? Is not automatically incrememented? Can the manual insert be assigned an arbitrary EVENTID? - This would render the EVENTID meaningless, wouldn't it?

Reason why eventid alone doesnt work is because users can insert stock events in history.

If user now creates a stock event for 2015 it would have the highest eventid as the newest event and would return the stock of 2015 instead of most recent one from today.

Got it!

 

I will take another look.

 

Thanks

The only way that seems to work is to add another table with calculated columns. I'm not sure how this will work when scaled up but it does produce the desired results.

 

Let me know if it works for you. Thanks

 

The calculated columns are

MaxDate =
VAR ItemID = ItemLocations[ITEMID]
VAR Warehouse = ItemLocations[WAREHOUSE]
VAR Locations = ItemLocations[LOCATION]
VAR MaxDate =
CALCULATE (MAX ( InventoryEvents[EVENTDATETIME] ),
FILTER ( InventoryEvents, InventoryEvents[ITEMID] = ItemID && InventoryEvents[WAREHOUSE] = Warehouse
&& InventoryEvents[LOCATION] = Locations) )
RETURN
MaxDate


MaxEvent =
VAR ItemID = ItemLocations[ITEMID]
VAR Warehouse = ItemLocations[WAREHOUSE]
VAR Locations = ItemLocations[LOCATION]
VAR MaxEvent =
CALCULATE (MAX ( InventoryEvents[EVENTID] ),
FILTER ( InventoryEvents, InventoryEvents[ITEMID] = ItemID && InventoryEvents[WAREHOUSE] = Warehouse
&& InventoryEvents[LOCATION] = Locations) )
RETURN
MaxEvent


StockQty =
CALCULATE(
[TotalQty],
FILTER(RELATEDTABLE(InventoryEvents), InventoryEvents[ITEMID] = ItemLocations[ITEMID]
&& InventoryEvents[EVENTDATETIME] = ItemLocations[MaxDate]
&& InventoryEvents[EVENTID] = ItemLocations[MaxEvent]
&& InventoryEvents[WAREHOUSE] = ItemLocations[WAREHOUSE]
&& InventoryEvents[LOCATION] = ItemLocations[LOCATION] )
)

 

INVENTORY EVENTS.jpg

 

View solution in original post

Thank you @glissando 

Your solution seems to work like supposed to but as you meationed it might have some issues when scaled up.

I came into conclusion that this might be a little tricky thing to do with DAX so I'm now cleaning the data in SQL to simplify things so there would be just a single latest date for each item when loaded into PBI.

Super User IV
Super User IV

@Juksho , Try

Measure =
VAR __id = MAX ( 'Table'[ITEMID] )
VAR __date = CALCULATE ( MAX( 'Table'[EVENTDATETIME] ), ALLSELECTED ( 'Table' ), 'Table'[ITEMID] = __id )
RETURN CALCULATE ( sum ( 'Table'[QTY] ), VALUES ( 'Table'[ITEMID ), 'Table'[ITEMID] = __id, 'Table'[EVENTDATETIME] = __date )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you @amitchandak . Your measure doesn't seem to take into account the EVENTID and instead just returns the latest event by EVENTDATETIME only. For example user can input today new event for 1.1.2015 12:00 and if that timestamp already has an event there will be duplicate rows where the one with hightes eventid needs to be picked.

@Juksho , try with event id

Measure =
VAR __id = MAX ( 'Table'[ITEMID] )
VAR __date = CALCULATE ( MAX( 'Table'[EVENTID] ), ALLSELECTED ( 'Table' ), 'Table'[ITEMID] = __id )
RETURN CALCULATE ( sum ( 'Table'[QTY] ), VALUES ( 'Table'[ITEMID ), 'Table'[ITEMID] = __id, 'Table'[EVENTID] = __date )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors