Getting date for max value

[trixie_dreadful]

Hello!

I'm looking at COVID daily data. 

My main table looks like this:

dateRep	deaths	countriesAndTerritories
3/1/2020	1	Australia
3/1/2020	35	China
3/1/2020	9	Iran
3/1/2020	8	Italy
3/1/2020	2	Japan
3/1/2020	1	South_Korea
3/1/2020	1	United_States_of_America
3/2/2020	42	China
3/2/2020	11	Iran

I have created a second table for my consolidated deaths data, using GROUPBY

DeathsbyCountry = GROUPBY(data, data[continentExp],data[countriesAndTerritories], "TotalDeath", SUMX(CURRENTGROUP(),data[deaths]), "Max Death", MAXX(CURRENTGROUP(),data[deaths]))

This gives me the following:

data_continentExp	data_countriesAndTerritories	TotalDeath	Max Death
America	United_States_of_America	121228	4928
Europe	France	29720	2004
Asia	India	14476	2003
America	Brazil	52645	1473
Asia	China	4640	1290
Europe	Spain	28325	1179
Europe	United_Kingdom	42927	1172
America	Mexico	23377	1091

Now, here's the BIG question - how do I find the date that is related to that Max Date?  

[amitchandak]

@trixie_dreadful , what is the objective of the second table?

Like I want last day of data then i would do like

new Table =
var _max = maxx(Table1,Table1[Date])
return 
calculate(Table, filter(Table,Table[Date]=_max))

The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[trixie_dreadful]

Hi,

The first table (data) is the daily report of deaths by country:

Here is that example:

My main table looks like this (this is a truncated sample example).  The actual data is 25k rows.  

dateRep	deaths	countriesAndTerritories
3/1/2020	10	Australia
3/1/2020	35	China
3/1/2020	9	Iran
3/1/2020	8	Italy
3/1/2020	2	Japan
3/1/2020	1	South_Korea
3/1/2020	1	United_States_of_America
3/2/2020	3	Australia
3/2/2020	42	China
3/2/2020	11	Iran
3/2/2020	14	Italy
3/3/2020	4	United_States_of_America

The second table is to aggregate the COVID deaths by country, then return the date of the most deaths and the number of deaths on that day.  

Using the example data above,  the results would be:

countriesAndTerritories	TotalDeaths	MaxDeaths	MaxDeathDate
Australia	13	10	3/1/2020
China	77	42	3/2/2020
Iran	9	9	3/1/2020
Italy	22	14	3/2/2020
Japan	2	2	3/1/2020
South_Korea	1	1	3/1/2020
United_States_of_America	5	4	3/3/2020

The MaxDeathDate, isn't the most recent Date, its the date on which the MaxDeaths occured.  

Here's the expected result from the actual data:

data_countriesAndTerritories	TotalDeath	MaxDeaths	MaxDeathsDate
United_States_of_America	121228	4928	4/16/2020
France	29720	2004	4/4/2020
India	14476	2003	6/17/2020

Its the column MaxDeathDate that I can't determine.

[v-zhenbw-msft · Accepted solution]

Hi @trixie_dreadful ,

We can create a calculate column in your calculate table.

Create a new column named MaxDeathDate in DeathsbyCountry table.

MaxDeathDate =
CALCULATE (
    MAX ( 'Table'[dateRep] ),
    FILTER (
        'Table',
        'Table'[countriesAndTerritories] = DeathsbyCountry[Table_countriesAndTerritories]
            && 'Table'[deaths] = DeathsbyCountry[Max Death]
    )
)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.