Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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] )
)
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.
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.
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] )
)
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.
I posted the pbix file here..
https://www.dropbox.com/sh/onpbp7y9psm9892/AADXNOseSFMdP0ADbrVzAqOAa?dl=0
@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 )
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
98 | |
39 | |
30 |