Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 inve...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

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

      

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

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

    Hope this helps.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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!