Forum Discussion

cecep's avatar
cecep
Frequent Visitor
4 years ago

Count values filtering for latest available value

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 a...
Need Help
  • cecep's avatar
    cecep
    Frequent Visitor
    4 years ago

    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!