Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone. I have a list of inventory from multiple locations that was taken on different dates. I need to know the current total inventory for each item using the last date posted from each location. Here is an example of my data
Date | Location | Item | Quantity |
3/31/2020 | Location A | Item 4 | 0 |
3/31/2020 | Location A | Item 1 | 156 |
3/31/2020 | Location A | Item 2 | 0 |
3/31/2020 | Location A | Item 3 | 0 |
4/10/2020 | Location A | Item 4 | 0 |
4/10/2020 | Location A | Item 1 | 0 |
4/10/2020 | Location A | Item 2 | 723 |
4/10/2020 | Location A | Item 3 | 377 |
4/14/2020 | Location B | Item 4 | 92 |
4/14/2020 | Location C | Item 4 | 59 |
4/14/2020 | Location E | Item 4 | 12 |
4/14/2020 | Location B | Item 1 | 1 |
4/14/2020 | Location C | Item 1 | 0 |
4/14/2020 | Location E | Item 1 | 6 |
4/14/2020 | Location B | Item 2 | 19 |
4/14/2020 | Location C | Item 2 | 14 |
4/14/2020 | Location E | Item 2 | 6 |
4/14/2020 | Location B | Item 3 | 3 |
4/14/2020 | Location C | Item 3 | 7 |
4/14/2020 | Location E | Item 3 | 12 |
4/15/2020 | Location E | Item 4 | 6 |
4/15/2020 | Location D | Item 4 | 1 |
4/15/2020 | Location F | Item 4 | 43 |
4/15/2020 | Location G | Item 4 | 16 |
4/15/2020 | Location E | Item 1 | 0 |
4/15/2020 | Location D | Item 1 | 0 |
4/15/2020 | Location F | Item 1 | 0 |
4/15/2020 | Location G | Item 1 | 1 |
4/15/2020 | Location E | Item 2 | 6 |
4/15/2020 | Location D | Item 2 | 23 |
4/15/2020 | Location F | Item 2 | 12 |
4/15/2020 | Location G | Item 2 | 51 |
4/15/2020 | Location E | Item 3 | 6 |
4/15/2020 | Location D | Item 3 | 0 |
4/15/2020 | Location F | Item 3 | 110 |
4/15/2020 | Location G | Item 3 | 10 |
4/16/2020 | Location A | Item 4 | 0 |
4/16/2020 | Location A | Item 1 | 452 |
4/16/2020 | Location A | Item 2 | 611 |
4/16/2020 | Location A | Item 3 | 1525 |
4/17/2020 | Location A | Item 4 | 0 |
4/17/2020 | Location A | Item 1 | 452 |
4/17/2020 | Location A | Item 2 | 481 |
4/17/2020 | Location A | Item 3 | 1475 |
Hi @SharonCNE ,
Try those measures:
Ricardo thanks for answering so quickly - im getting this error
The function SUM cannot work with values of type String
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
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?
Hi @SharonCNE ,
Try this measure:
Ricardo - that was it!! thank you. Much more complicated than I could have figured out 🙂
Nice!
Did I answer your question? Mark my post as a solution!
Ricardo
Can you share your pbix ?
Did I answer your question? Mark my post as a solution!
Ricardo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |