How to return the last value of my stock

[sassoumani]

Hello everybody ,

I have a table with the value of my stock every day by item.

item id	year	month	day	date	values
116	2023	01	01	01/01/2023	150
116	2023	01	15	15/01/2023	200
116	2023	01	31	31/01/2023	250
116	2023	02	28	28/02/2023	100

The user can select either the year, the month, or the date to get the stock result.

If the user displays the year, I want the latest stock value for the year.

Example for 2023 ==> result = 100 (last known value for 2023)

If it is the month, the last stock value for the month

Example Jan/2023 ==> result = 250  (last value for 01/2023)

If it is the day (date), the value of the stock on date

Example 15/01/2023 ==> result = 200  (value on date)

Thanks for your help.

[MayilVahanan]

Hi

Try like below

Sum({<date={'$(=Date(Max(date)))'}>}values)

it will display the max date info

[sassoumani]

Hi @MayilVahanan  , thanks for you reply.

I tried with your suggestion.

when I display the year and month dimensions, I have the result only for the last current month.

Example: I have the information for the month of June/2023 but not for the previous months. 

ID de l'article	an	mois	journée	Date	valeurs
116	2023	01	01	01/01/2023	0
116	2023	01	15	15/01/2023	0
116	2023	01	31	31/01/2023	0
116	2023	06	19	19/06/2023	100

[MayilVahanan]

Hi

Try like below

Dim: 

ID de l'article

an

mois

journée

Measure: Sum(Aggr(FirstSortedValue(valeurs, -Date), an, mois))

[sassoumani]

Hi ,

Thanks for you return but unfortunnaly it's not ok 😕 

When I choose to show Year and Month , I have this : (it's seems ok)

ITEM ID	YEAR	MONTH	Values
116	2023	01	250
116	2023	02	100

But when I want show only Year , the expected result is not good, it makes a sum instead of returning me the last known value of the year

ITEM ID	YEAR	Values
116	2023	350

below is the expected result

ITEM ID	YEAR	Values
116	2023	100

100 is the last value of the stock for 2023.

[sassoumani]

ITEM ID	YEAR	MONTH	Values
116	2023	01	250
116	2023	02	100

[MatheusC]

Hi @sassoumani ,
I would try something like this, see if it helps you.

sum(

Aggr(

    if(

        max(total<MONTH,YEAR>Values)=Values,

sum({<YEAR={$(=Max(YEAR))}>}Values)),MONTH,YEAR,Values))

[Kushal_Chawda]

@sassoumani  try below

=FirstSortedValue(distinct total <item_id,year,month>values, -date)