Forum Discussion
getting date for max value
Hi
I am trying to get the date for a maximum value,
I am using a groupby to get the maximum value from a table, then I would like to get the date where the maximum value was. Here is a section of my data.
LOCATION REPORT_DATE Totals
location_A 2017-01-01 19348
location_A 2017-01-02 16558
location_A 2017-01-03 18566.5
location_A 2017-01-04 19460
location_A 2017-02-28 17445
location_A 2017-03-01 14983.5
location_A 2017-03-02 18488.5
Location_B 2017-01-01 19348
Location_B 2017-01-02 16558
Location_B 2017-01-03 18566.5
Location_B 2017-01-04 19460
Location_B 2017-02-02 15238.5
Location_B 2017-02-03 15521
Location_B 2017-03-12 16361.5
Location_B 2017-03-29 17774
Location_B 2017-03-30 17512
So the data is groupby location, I get the max value using something like maxx(currentgroup(),[Totals])
So what I need next is to find out the date for that maximum value.
I tried using the RELATED function but it did not work - after creating indexes with Location and values.
Please advise.
Many Thanks
No idea, it worked for me.
5 Replies
Perhaps something like:
Measure 6 = VAR myLocation = MAX(Table7[LOCATION]) VAR tmpTable = GROUPBY(Table7,Table7[LOCATION],"MaxTotal",MAXX(CURRENTGROUP(),[Totals])) VAR myMax = MAXX(tmpTable,[MaxTotal]) RETURN MAXX(FILTER(ALL(Table7),[LOCATION]=myLocation && [Totals]=myMax),[REPORT_DATE])
Many Thanks Greg, it is almost working but I think the issue is with
VAR myLocation = MAX(Table7[LOCATION])
This returns a number that I can't compared with my location on
RETURN MAXX(FILTER(ALL(Table7),[LOCATION]=myLocation && [Totals]=myMax),[REPORT_DATE])
But I think what I need to get is my location from my currentgroup
create here.
GROUPBY(Table7,Table7[LOCATION],"MaxTotal",MAXX(CURRENTGROUP(),[Totals]))
No idea, it worked for me.
