Sum Distinct Stocks of Product per Date

[brunolovatti]

Hi!

In the sample table

Date	Product	Order	Stock
12/01/2022	A	-	100
12/01/2022	C	O1	50
13/01/2022	A	O2	120
13/01/2022	A	O3	120
13/01/2022	B	O4	20
13/01/2022	C	-	0

I need to SUM the last stock per each product dimension.

Date	Product	Stock
13/01/2022	A	120
13/01/2022	B	20
13/01/2022	C	0

The SUM of the last stock is 140 and if there is more than one record per day per product the stock value is always the same, product A in the example.

IF we had only one record per date + product, the measure would be:

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
[Stock]
)

But our data repeats the stock for the number of orders of each product per day.

I tried to use FirstSortedValue and aggr distinct with set analysis but could not get it to work yet.

Thank you!

Bruno

[brunobertels]

Hi 

May be this 

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"},Order={"$(=max({1}Order))"}
>
}
[Stock]
)

[brunolovatti]

Did not work. It returns 20. Should be 140.

Thank you!

Sample data is:

orders:
Load
Date(date) as data, order, product, stock
Inline
[date, order, product, stock
'13/01/2021', null, 'A', 100
'13/01/2021', 1, 'C', 50

'14/01/2021', 2, 'A', 120
'14/01/2021', 3, 'A', 120
'14/01/2021', 4, 'B', 20
'14/01/2021', null, 'C', 0
];

[brunolovatti]

This worked for this sample:

Sum(
{
1
<
[date]={"$(=Max({1}date))"}
>
}
distinct stock
)

And for the original project i think this is correct too:

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
aggr(
Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
distinct [stock]
)
,DateStoreProduct
)
)

With a combined key (date + store + product).