Forum Discussion
Inventory at multiple locations and multiple dates
Ricardo thanks for answering so quickly - im getting this error
The function SUM cannot work with values of type String
Community Champion
Be sure the quantity column is numeric.
Did I answer your question? Mark my post as a solution!
Ricardo
Ricardo - yes value is a whole number :). the item is Text but I can't change that
- camargos88
Can you share your pbix ?
Did I answer your question? Mark my post as a solution!
Ricardo Ricardo - I was just looking at your result table and It's actually the result I'm looking for:
If you look at the data provided
Item 3 has 10 entries
3/31/20 location A 0
4/10/20 location A 377
4/14/20 location B 3
4/14/20 location C 7
4/14/20 location E 12
4/15/20 location F 110
4/15/20 location G 10
4/16/20 location A 1525
4/17/20 location A 1475
the measure I am trying to create would look at that - determine the latest date for EACH location and SUM all those together - in this example it would be the amounts shown for B + C + E + F + G and ONLY the amount for A on 4/17/20 (because it was the latest date it was reported at that location)
and so on and so forth. Does that make sense?
- camargos88
Hi SharonCNE ,
Try this measure:
Measure =SUMX(ADDCOLUMNS(SUMMARIZE('Table';'Table'[Item];'Table'[Location];"Max"; MAX('Table'[Date]));"Value"; CALCULATE(SUM('Table'[Quantity]); FILTER('Table'; 'Table'[Item] = EARLIER('Table'[Item]) && 'Table'[Location] = EARLIER('Table'[Location]) && 'Table'[Date] = [Max])));[Value])Did I answer your question? Mark my post as a solution!
Ricardo