Ignore column series in a measure

[ElChambre20]

Hi All,

This is actually a follow up post from the this questionCalculate Average Using Dates from a Measure where i was attempting to calculate the average survey score up to a certain date in time, only using the most recent survey.

With some help I managed to get it working, Thanks @AIB. Im now having a new problem so wanted to create a fresh post.

Essentially i want to plot the same information in a column graph, using the survey score as the column series, so you see how many surveys got each score up to and including a date in time (again, using only the most recent survey date).

I have adapted the measure that worked from the previous post to count the rows where the survey is the last up to and including the selected year instead of averaging the score, but the problem is, when i plot this on a column graph, it shows the most recent survey, for each score for each year. See below, i have filtered the data down to a single location to betteer show the problem, so it should only show 1 column per year, but it is showing 2 columns form 2016 onwards as it it showing the last time it received a score of 5 and 6, rather than just the last score it received. The line graph is the average which was fixed in the last post (this works correctly).

So i need the DAX to ignore the column series in the graph and just return the most recent survey (as i did with the previous question). I have looked online and found many posts about using "ALL" to remove filters but i havent managed to get this to work with my measure. 

This is the measure im using currently:

AT SURVEY SCORE ACC_3 = 
CALCULATE(
    COUNTROWS(
        FILTER (
            CalcTableSiteVisitsWithRatings,
            VAR currentYear_ =
                CALCULATE(
                    MAX ( 'Date'[Date] ),
                    ALLSELECTED('Date')
                )
            VAR latestInYear_ =
                CALCULATE (
                    MAX ( CalcTableSiteVisitsWithRatings[EventDate] ),
                    CalcTableSiteVisitsWithRatings[EventDate]  <= currentYear_,
                    ALLEXCEPT (
                        CalcTableSiteVisitsWithRatings,
                        CalcTableSiteVisitsWithRatings[LocationID]
                    )
                )
            RETURN
                IF (CalcTableSiteVisitsWithRatings[EventDate] = latestInYear_,
                    TRUE(),
                    FALSE()
                )
        ) 
    ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Any help would be greatly appreciated, if you need any further info please let me know.

Thanks in advance

[v-jayw-msft]

Hi @ElChambre20 ,

Please share some sample data and expected result if you don't have any Confidential Information.

Best Regards,

Jay