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

[Juksho]

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:

ITEMID	EVENTID	WAREHOUSE	LOCATION	EVENTDATETIME	QTY
842039	931124	WH1	L11	1.1.2005 0:00	37504,24
842039	2303041	WH1	L11	4.1.2005 9:11	37600
842039	2282976	WH1	L11	4.1.2005 9:11	47600
842039	2302640	WH1	L12	13.1.2005 12:40	27504,24
842039	2303078	WH1	L12	13.1.2005 15:22	27504,24
842039	2303079	WH1	L12	13.1.2005 15:22	123269,2
842039	2303048	WH1	L12	13.1.2005 15:22	123269,2
842039	2309163	WH2	L22	14.12.2011 13:59	122269,2
842039	2325883	WH2	L22	14.12.2011 13:59	117269,2
842039	2349151	WH2	L22	14.12.2011 13:59	113269,2
842039	2392999	WH2	L22	14.12.2011 14:01	112269,2
842039	2397209	WH2	L22	14.12.2011 14:01	109269,2

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

Item	Warehouse	Location	Stock Qty
842039	WH1	L11	37600
842039	WH1	L11	123269,2
842039	WH2	L22	112269,2
843033	WH5	L51	450
844000	WH5	L52	100
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.

[Icey]

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

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]

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]

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?

[Juksho]

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.

[glissando]

Got it!

I will take another look.

Thanks

[glissando · 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] )
)

[Juksho]

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.

[glissando]

I posted the pbix file here..

https://www.dropbox.com/sh/onpbp7y9psm9892/AADXNOseSFMdP0ADbrVzAqOAa?dl=0 

[amitchandak]

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

[Juksho]

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.

[amitchandak]

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