Current Inventory Levels

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

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

[Anonymous]

Is this helpful to test formula?

Date	Item	Qty
3/2/2020	Item 1	1
3/20/2020	Item 1	2
3/19/2020	Item 2	4
2/28/2020	Item 2	1
3/1/2020	Item 3	1
1/16/2020	Item 4	0
2/21/2020	Item 4	4
3/19/2020	Item 4	3
12/6/2019	Item 5	2
2/10/2020	Item 5	2
3/21/2020	Item 5	3

[Ashish_Mathur · Accepted solution]

Hi,

The answer should be 13 (not 12).  You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thx so much Ashish!!!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler · Accepted solution]

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

[Anonymous]

Hi @GR ,

Thanks for your solution. One additional question: how do you need to modify the syntax if your dataset has the mutation for each item: in other words, in ReynoldTstan's example, for item 4 you want to show the cumulative number (0+4+3=7) instead of the last one (3)?

Thanks a lot for your help in advance!

[Anonymous]

Awesome...thx Greg!!!

[Greg_Deckler]

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.