topic Inventory at multiple locations and multiple dates in DAX Commands and Tips

Inventory at multiple locations and multiple dates

SharonCNE — Thu, 30 Apr 2020 18:44:56 GMT

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

Re: Inventory at multiple locations and multiple dates

camargos88 — Thu, 30 Apr 2020 19:10:31 GMT

Hi SharonCNE ,

Try those measures:

_LastDate = CALCULATE(MAX('Table'[Date]); ALLEXCEPT('Table';'Table'[Item]))

Measure = CALCULATE(SUM('Table'[Quantity]); FILTER(ALLEXCEPT('Table'; 'Table'[Item]); 'Table'[Date] = [_LastDate]))

Did I answer your question? Mark my post as a solution!
Ricardo

Re: Inventory at multiple locations and multiple dates

SharonCNE — Thu, 30 Apr 2020 19:44:36 GMT

Ricardo thanks for answering so quickly - im getting this error

The function SUM cannot work with values of type String

Re: Inventory at multiple locations and multiple dates

camargos88 — Thu, 30 Apr 2020 19:49:37 GMT

SharonCNE ,

Be sure the quantity column is numeric.

Did I answer your question? Mark my post as a solution!
Ricardo

Re: Inventory at multiple locations and multiple dates

SharonCNE — Thu, 30 Apr 2020 20:01:12 GMT

Ricardo - yes value is a whole number :). the item is Text but I can't change that

Re: Inventory at multiple locations and multiple dates

camargos88 — Thu, 30 Apr 2020 20:05:17 GMT

SharonCNE ,

Can you share your pbix ?

Did I answer your question? Mark my post as a solution!
Ricardo

Re: Inventory at multiple locations and multiple dates

SharonCNE — Thu, 30 Apr 2020 21:41:28 GMT

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?

Re: Inventory at multiple locations and multiple dates

camargos88 — Fri, 01 May 2020 13:48:56 GMT

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

Re: Inventory at multiple locations and multiple dates

SharonCNE — Fri, 01 May 2020 14:42:42 GMT

Ricardo - that was it!! thank you. Much more complicated than I could have figured out 🙂

Re: Inventory at multiple locations and multiple dates

camargos88 — Fri, 01 May 2020 14:44:52 GMT

SharonCNE ,

Nice!

Did I answer your question? Mark my post as a solution!
Ricardo