average for last date

[Madina]

Hello everyone,

I have a data which have date, product_name , sale.  I want to show the average for last date for particular product in gauge . The thing is that I have several records for each product  for each date. 

To make it clearer:

for example, product_name "a" has values  for sale 50,10,20,30,40 for january 2021, has values for sale 500,100,200,300,400 for february 2021.

SoI want that gauge shows  (500+100+200+300+400)/5  , because last date is february 2021.

Wanted to use FirstSortedValue( {$<product_name={'a'}>} sale, -date), but it takes null, because in this case for date we get several values

[abhijitnalekar]

Hi @Madina ,

Are you looking for the average for the max date month for the particular product?

and Is the divider(5) will be the constant for all the products or it will vary by product?  

[Madina]

hi, @abhijitnalekar 

no it may vary for each product, and even for any month. for example, this month you have 5 sales for product a, while on other month only 2.

And about date, yes, for max date, for example here max date is February