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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Juksho
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
Icey
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.

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

 

glissando
Resolver II
Resolver II

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

 

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.

amitchandak
Super User
Super User

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

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 )

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.