Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Is most recent date

I have a table that has a list of countries, date and value. This table is updated every month with the added new value for countries by month. Every country reports values aty different times and fo...
Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    When you take the MAX, it's calculating the maximum over the whole table instead of just for the current country.

     

    You can fix this using ALLEXCEPT to preserve the country row context:

    IsMostRecentdate =
VAR currentrowdate =
    FORMAT ( 'United_States,_New_Zealand_and_'[Date], "mm/dd/yyy" )
VAR ismostrecentdate =
    FORMAT (
        CALCULATE (
            MAX ( 'United_States,_New_Zealand_and_'[Date] ),
            ALLEXCEPT (
                'United_States,_New_Zealand_and_',
                'United_States,_New_Zealand_and_'[County]
            )
        ),
        "mm/dd/yyy"
    )
RETURN
    IF ( ismostrecentdate = currentrowdate, "yes", "no" )

     

AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

When you take the MAX, it's calculating the maximum over the whole table instead of just for the current country.

 

You can fix this using ALLEXCEPT to preserve the country row context:

IsMostRecentdate =
VAR currentrowdate =
    FORMAT ( 'United_States,_New_Zealand_and_'[Date], "mm/dd/yyy" )
VAR ismostrecentdate =
    FORMAT (
        CALCULATE (
            MAX ( 'United_States,_New_Zealand_and_'[Date] ),
            ALLEXCEPT (
                'United_States,_New_Zealand_and_',
                'United_States,_New_Zealand_and_'[County]
            )
        ),
        "mm/dd/yyy"
    )
RETURN
    IF ( ismostrecentdate = currentrowdate, "yes", "no" )