Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to Solution.
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" )
Hello @Anonymous
I just checked with the sample data and its doing fine here. The sample file is attached for your reference.
https://drive.google.com/file/d/1nmqGDh3EDv5onnoMXsPwGuPh3Oa62hvt/view?usp=sharing
Hope this helps.
Regards
Kumail Raza
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |