Forum Discussion
Getting date for max value
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.
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.
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.
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.
