cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmauldin
Helper I
Helper I

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 for different months. For example Here ar my most recent values and months

 

County     Date                Value

US            March 2021    1.15

NZ           May 2021       2.3

AU           April 2021      5.4

 

I am trying to add a column so I can filter "ismostrecetdate" for each Country as the dates are always different. I entered the below but then I only receive "yes" for NZ as it has the latest date.

 

IsMostRecentdate = var currentrowdate = FORMAT('United_States,_New_Zealand_and_'[Date], "mm/dd/yyy") var ismostrecentdate = FORMAT(MAX('United_States,_New_Zealand_and_'[Date]), "mm/dd/yyy") return IF(ismostrecentdate = currentrowdate, "yes", "no")
 

Can anyone hlep? The purpose of this is so I can add smart text and values in a text box that automatically update data based off the most recent date of the country I am writing about 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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" )

 

View solution in original post

2 REPLIES 2
Kumail
Continued Contributor
Continued Contributor

Hello @rmauldin 

 

I just checked with the sample data and its doing fine here. The sample file is attached for your reference.

Kumail_0-1624983528107.png

 

https://drive.google.com/file/d/1nmqGDh3EDv5onnoMXsPwGuPh3Oa62hvt/view?usp=sharing

 

Hope this helps.

 

Regards

Kumail Raza

AlexisOlson
Super User
Super User

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" )

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.