Forum Discussion
Current Inventory Levels
I need to figure out a measure that calculates the current inventory level based on items last inventory entry into the table. Below is an example of 5 items and to the right I flagged the last inventory date of each item. The sum of the 5 items inventory on the last day they were inventoried is 12. The result of the measure needs to be 12.
Bingo Anonymous , that's great. Did the trick. Definitely some syntax errors and I missed an EARLIER. PBIX is attached.
VAR __Table = ADDCOLUMNS( GROUPBY( 'Table', [Item], "__LastDate",MAXX(CURRENTGROUP(),[Date]) ), "__LastInventory",MAXX(FILTER('Table','Table'[Date] = [__LastDate] && 'Table'[Item] = EARLIER([Item])),'Table'[Qty]) ) RETURN SUMX(__Table,[__LastInventory])
9 Replies
Ha! I literally just wrote this quick measure today. This is the first time I get to use it! 🙂
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
That's the pattern, you will need to adapt it a bit. Let me know if you need further assistance in adapting it.
Well, no sample data posted as text so this DAX will likely have some syntax errors in it, but I am suuuuupppper bored because everthing is shut down with this COVID-19 stuff going on. Plus I realized this is a bit of a different pattern, although you could have still used that pattern. But something like this might be better:
Current Inventory Measure = VAR __Table = ADDCOLUMNS( SUMMARIZE( 'Table', [Item 1], "__LastDate",MAX([Date]) ), "__LastInventory",MAXX(FILTER('Table','Table'[Date] = [__LastDate] && 'Table'[Item 1] = [Item 1],'Table'[Qty]) ) RETURN SUMX(__Table,[__LastInventory])Oh, and sample data posted as text is always great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Bingo Anonymous , that's great. Did the trick. Definitely some syntax errors and I missed an EARLIER. PBIX is attached.
VAR __Table = ADDCOLUMNS( GROUPBY( 'Table', [Item], "__LastDate",MAXX(CURRENTGROUP(),[Date]) ), "__LastInventory",MAXX(FILTER('Table','Table'[Date] = [__LastDate] && 'Table'[Item] = EARLIER([Item])),'Table'[Qty]) ) RETURN SUMX(__Table,[__LastInventory])