Max Date for each year

[faroukfrk]

Hello guys,

I want to ask you about making a  set analysis  that calculates the sum of stock value at  "the Max Date"  for each year

supposing that I have the stock value for the 2 years 2018 and 2019

i want to get the stock value for both max 2018  "31-12-2018"and 2019  "31-03-2019"

I have written a set analysis like this

Aggr(sum({<date={"=Aggr(Max(FQST_Date_Stock),[Année])"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant),[Année])

but it gives me the value of Jan-2018 and Jan-2019

like in the picture :

for Jan-2019 it's the value of '01-01-2019'

for Jan-2018 it's the value of '31-01-2018' (the only date in Jan-2018 the system began from that date)

thank you for helping me cherished community  🙂

[Channa]

sum({<date={"=Aggr(Max(FQST_Date_Stock),[Année])"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)

[faroukfrk]

Sorry Chenna,

it doesn't give the right result

thank you for the help 🙂

[Marcos_rv]

Try this:

=sum({<date={"$(=Max(FQST_Date_Stock))"},[TypeDate]={'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant) 

[sunny_talwar]

Try this may be

If([Année-Mois] = Max(TOTAL <Année> [Année-Mois]),
Sum(TOTAL <Année> {<[TypeDate] = {'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)

[faroukfrk]

Hello Sunny,

thank you for the response it hilights the last year-month for each year.

However, is there anything else  to add so that it gives me "the stock value" of  last date for each month  i mean "31-12-2018"  and "31-03-2019"

we are almost there 😉

PS Would you please explain me what does that set analysis do and the meaning of <Année> does that mean "over years" ?!

Thank you a lot

[Channa]

If([Année-Mois] = Max(TOTAL <Année> [Année-Mois]),
Sum(TOTAL <Année> {<[TypeDate] = {'DateStock'}, [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)

IF YOU REMOVE TOTAL FROM THE EXPRESSION YOU WILL GET VALUE OF THE DATE 

[faroukfrk]

Hello again,

I did this change to the set analysis that you've proposed @sunny_talwar 

If([date] = Max(TOTAL <Année> [date]),
Sum( {< date = {"=Max(TOTAL <Année> [date])"},[TypeDate] = {'DateStock'},  [FQST_Type]={'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
)

and it gives me the right calculation  for the dates 31-12-2018 and 31-03-2019 as in the picture below

But when I delete the date.Date dimension letting just Année it gives me a null result 

[faroukfrk]

Nope @Channa ,

that gives a Null Value :'(

thanks for trying

you would see the modification I made to @sunny_talwar 's expression

it's almost there I think I need some tip that makes a year represented by its max date instead

Thank you

[sunny_talwar]

Try this

FirstSortedValue({<[TypeDate] = {'DateStock'},  [FQST_Type] = {'StockInterne'}>} Aggr(
  Sum({<[TypeDate] = {'DateStock'},  [FQST_Type] = {'StockInterne'}>} FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)
, Date), -Date)