Last monthly amount for each quarter using set analysis

[deblina_rai]

Input DAta:

Product type	Asset Year	Asset  Quarter	Asset Month	Amount
MF	2019	Q1	JAn	39M
MF	2019	Q1	Feb	33M
MF	2019	Q1	Mar	40M
MF	2019	Q2	Apr	50M
MF	2019	Q2	MAy	67M
MF	2019	Q2	Jun	41M

Out put data: I need  latest month amount for each quarter while selecting two month like below

Product type	Asset Year	Asset  Quarter	Amount
MF	2019	Q1	40M
MF	2019	Q2	41M

[tresesco]

Try using FirstSortedValue() , like:

=FirstSortedValue(Amount, -Month)                 //This would work if your month field is a dual field

Also, if you have multiple transactions against a single month, you have to use sum() along with aggr(), like:

=FirstSortedValue(Aggr(Sum(Amount), Month), -Month)

And if your month field is a text field, try like:

=FirstSortedValue(Aggr(Sum(Amount), Month), -Date#(Month, 'MMM'))

[deblina_rai]

Its is working for one product but if i select two product type as dimension or if there is no filter then the chart giving wrong blank values for some product. 

Can you please give any other way ?

[sunny_talwar]

May be try this

=FirstSortedValue(
   Aggr(
     Sum(Amount)
   , [Product type], [Asset Year], [Asset Quarter], [Asset Month]),
  -Aggr(
     [Asset Month]
   , [Product type], [Asset Year], [Asset Quarter], [Asset Month])
)

[deblina_rai]

 actually i need to make this measure as dynamic master measure so that it can work for any dimension. So we cant add Product type in aggr level. Product type   can be change to product name or something else.

[sunny_talwar]

How do you change the dimension in the chart? May be you can use the same logic to change the dimension in your expression

[deblina_rai]

i am creating master measure for a self service app. So this measure will be used in any chart . So basically requirement is to make two measure one for latest month amount of each quarter & latest month amount of each year.

[sunny_talwar]

May be use GetObjectField to dynamically get the dimension from your object. This might not be perfect, but you can play around with the idea to make this work for you.

=FirstSortedValue(
   Aggr(
     Sum(Amount)
   , $(=GetObjectField(0)), $(=GetObjectField(1)), $(=GetObjectField(2)), [Asset Month]),
  -Aggr(
     [Asset Month]
   , $(=GetObjectField(0)), $(=GetObjectField(1)), $(=GetObjectField(2)), [Asset Month])
)

[deblina_rai]

i got your point. But i try  this option , i didn't find any mistakes in the  syntax but its giving invalid.

IS there any other option with out using fisrtsortedvalue function?

May be my number of dimensions also vary .

[deblina_rai]

No of dimension can vary  as its self service app. So is there any other way to achieve this  with out using first sorted value function?