Forum Discussion

trixie_dreadful's avatar
trixie_dreadful
Microsoft Employee
6 years ago
Solved

Getting date for max value

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/...
Need Help
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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's avatar
trixie_dreadful
Microsoft Employee
6 years ago

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:

countriesAndTerritoriesTotalDeathsMaxDeathsMaxDeathDate

Australia

13103/1/2020
China77423/2/2020
Iran993/1/2020
Italy22143/2/2020
Japan223/1/2020
South_Korea113/1/2020
United_States_of_America543/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_countriesAndTerritoriesTotalDeathMaxDeathsMaxDeathsDate
United_States_of_America12122849284/16/2020
France2972020044/4/2020
India1447620036/17/2020

 

Its the column MaxDeathDate that I can't determine.

 

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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.