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/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_continentExpdata_countriesAndTerritoriesTotalDeathMax Death
AmericaUnited_States_of_America1212284928
EuropeFrance297202004
AsiaIndia144762003
AmericaBrazil526451473
AsiaChina46401290
EuropeSpain283251179
EuropeUnited_Kingdom429271172
AmericaMexico233771091

 

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

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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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'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.