Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I would like to create the second expression based on the same set of the data,
the below first expression is about the highest total quantity among the 30 order date (or within period).
say, the highest total quantity is on 21 Aug 2017 in Aug 2017
Max(Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date]))
how can I get the 21 Aug 2017 as second expression in KPI?
Thank you so much
Hi
may be this :
firstsortedvalue ( [Order Date] , - Max(Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date])) )
in fact this one
firstsortedvalue ( [Order Date] , - (Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date]))
Bruno
Please share sample data.
Thanks
Neha
I don't understand what's your expected result for the second expression in KPI.
When he uses this expression in the first measure:
Max(Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date]))
he gets as a result: 350 for example; and he knows that this is Quantity for the Order Date = 21 aout 2017
So, he wants, in the second measure ; to show 21 Aout 2017 (he can show this as a title for example..)
I'm working on it
maybe this:
only( Aggr( if( [Order Date] = Max( TOTAL Aggr( sum( { $< item = {'ABC'} >} [Quantity] ), [Order Date] ) ), [Order Date], null() ), [Order Date] ) )
Thank you Omar , for adding the explanation.
Hi
may be this :
firstsortedvalue ( [Order Date] , - Max(Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date])) )
in fact this one
firstsortedvalue ( [Order Date] , - (Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date]))
Bruno
Thank you Bruno,
it works.
date(firstsortedvalue ( [Order Date] , - (Aggr(sum({$<item={'ABC'}>}[Quantity]), [Order Date]))),'dd-MMM-yyyy')