Count values filtering for latest available value

[cecep]

Hello all, 

I have an issues that hours of google, youtube and scrolling the platform haven't resolved... hopefully your wisdom will break this nightmare! 

I have a table with countries who answer to the same question every year. I need to count, for each country, what is the latest available response. 

Where it gets tricky is that I want the "last date" to be based on a slicer where users can change: for example they want the count when the last date is 2013, or when the last date is 2021.

I have tried COUNT functions with FILTERS, I tried using LOOKUPVALUE, I have tried combining measures looking for Min Date, Max Date etc. 

Everytime my formulas either don't select the correct values to count or select all values for years contains in the slicer... It seems I can't seem to ask the formula to look for the latest value, for each country seperately.

Would anybody have any idea how to go about this? 

Here is an extract, if the user selects latest date to be 2021, in red should be the answers selected:

If the user selects 2018 to be the latest date, in green should be the answers selected:

Thank you in advance!

[v-yangliu-msft]

Hi  @cecep .

Here are the steps you can follow：

1. Create measure.

Measure =
var _max=MAXX(ALLSELECTED(Sheet1),[YEAR])
var _1=SUMMARIZE(FILTER('Sheet1',[YEAR]<=_max),[ANSWER],[COUNTRY])
return
COUNTX(_1,[COUNTRY])

2. Result:

When I count in the sample data, the opt-In is also 43. You can check how much the formula shows in your data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cecep]

Hi Liu, 

Many thank for giving this a try! Actually the issue is that for every "last year" selected I need only one value per country (where one value is available, searching from "last year" to each previous year). 

So when I verified this data manually, the count for when 2017 is the last year is 93 individual countries (a number I do manage to confirm on power bi). Which means that 66 and 43 cannot be true as it adds up to 109. 

What I need is for the formula to select the latest value and ignore any other values that may exist... 

For reference I checked manually the data in book 1 [Sheet 2] and updated it in the folder I shared. Result should be: 

OPT IN = 55

OPT OUT = 35

TOTAL = 93 

I am amazed at what that 66 and 43 I keep getting correspond to 😂

Your formula I think is the good approach but how do we tell it to ignore other values once it finds one? 

Many thanks again,

Best wishes, 

Célia. 

[vivran22]

Hello @cecep ,

Your requirement is not clear to me from the example you have shared. Can you share a sample file/pbix file with the expected outcome as an example?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[cecep]

Hi Vivek, 

Very grateful for your prompt interest! 

Here you go, sorry it wasn't clear before. https://drive.google.com/drive/folders/1vaadc7dHBZksTwo2Bd3LfFgBlFaoz6rp?usp=sharing 

Basically here is what the output will look like: 

OPT IN = 55 countries 

OPT OUT = 38 countries

I need a filter or measure that will for each country, take the latest available answer only for the latest year selected by the user in the slicer. 

Right now all my solutions either: 

• Count all available answers within the range selected, for example if the user selects 2015 - 2017, and if Country A responded to the question every year, then it counts 3 
• Count only one answer per country but does not select the latest answer therefore invalidating the data, as countries do change answers from year to year. 

Do note if it is easier I am fine with the slicer being "less or equal to" or list option rather than "between" as long as for whatever the last year selected, the formula will lookback on previous available data for each country.

Let me know if this is better! 

Cheers!