Count formula with filter for Max Year

Any type of aggregation (count, sum, avg) should be an measure. As a rule of thumb, try not to use calculated columns unless you want that column to be used in the axis, row, or slicer on a report. Anything in your "Values" should be a measure.

Measures are dynamic based on the fields used in your chart, plus any additional filters on the page. Which can be a great thing because it gives you flexibility, but it can be hard to troubleshoot issues like you are describing because I don't know the full context in your report.

In other words, a single measure can mean two very different things depending on what data is selected in the report page itself, either via the visual or page filters. MAX([Year]) does not necessarily = 2018. It means "max year in the current filter context." So if you have 2017 selected in a slicer, MAX([Year]) = 2017.

That's a long way of saying I'm sorry my suggestion is not working, but if you want to enter the values specifically into the measure, it should work. You will just need to update the measure in 2019 :smileyhappy:

And if you are doing that, it's probably best to avoid using FILTER() entirely. Simply, CALCULATE( COUNT( [ Created Year]) , 'Marketing Snapshot'[Created Year] = 2018, ...) is a good practice.

Thanks, that is all very helpful!

I am not using any page or visual filters, which is why I was surprised the extra data was coming through.

I am hoping there is a dynamic way to do this, as I also will have to update the snapshot date each month.

jmalone

Is there a way to take a MAX of the whole table, no matter the context, so even if a subset drill down has 2014-2017 in the data, it still looks at the whole to know 2018 is the MAX?

You could try the previous formula without ALL( ), as shown below. I hesitate to say this will be the end-all solution for you, but it's worth a shot :)

CY MGL # =
CALCULATE (
    COUNT ( 'Marketing Snapshot'[CreateDate] ),
    FILTER (
        'Marketing Snapshot' ,
        'Marketing Snapshot'[CreateDate Year]
            = MAX ( 'Marketing Snapshot'[CreateDate Year] )
            && 'Marketing Snapshot'[DateStampMonth]
                = MAX ( 'Marketing Snapshot'[DateStampMonth] )
    ),
    'Marketing Snapshot'[OpportunityOrigin] = "Marketing"
)

This will only work if the [DateStampMonth] column is a number. If it uses the month name (like "January" or "Jan"), the MAX() will not help. Otherwise you could use 

MONTH ( Marketing Snapshot'[CreateDate] ) = MONTH ( MAX( Marketing Snapshot'[CreateDate] ) )

for the month clause. Assuming [CreateDate] is a 'date' data type.

Thanks, that one was accepted, but its still showing the extra data that shouldn't be getting through the filter.

What does your data model look like? Is everything in a single table?

Correct, its all one table