Forum Discussion
Helper IIICount formula with filter for Max Year
Current Year SAO Records := CALCULATE ( COUNT ( 'TableName'[SAO Date] ), FILTER ( ALL ( 'TableName'[SAO Year] ), 'TableName'[SAO Year] = MAX ( 'TableName'[SAO Year] ) ) )
Helper IIII have found that is has to do with two of the filters, apparently when placed in different context, they don't hold up and allow records outside of the scope of current year and latest snapshot date.
Any advice?
1 - ALL ( 'Marketing Snapshot'[CreateDate Year] ),
'Marketing Snapshot'[CreateDate Year] = MAX ( 'Marketing Snapshot'[CreateDate Year] )
)
2 - FILTER(ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = MAX ( 'Marketing Snapshot'[DateStampMonth] )
)
Full Measure:
CY MGL # =
CALCULATE (
COUNT ( 'Marketing Snapshot'[CreateDate] ),
FILTER (
ALL ( 'Marketing Snapshot'[CreateDate Year] ),
'Marketing Snapshot'[CreateDate Year] = MAX ( 'Marketing Snapshot'[CreateDate Year] )
),
FILTER('Marketing Snapshot','Marketing Snapshot'[OpportunityOrigin]="Marketing"),
FILTER(ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = MAX ( 'Marketing Snapshot'[DateStampMonth] )
))
Resolver IIISorry I hadn't seen your follow-up in my first response.
Maybe try this formula. The difference is both conditions are moved within the same FILTER function.
CY MGL # =
CALCULATE (
COUNT ( 'Marketing Snapshot'[CreateDate] ),
FILTER (
ALL ( 'Marketing Snapshot' ),
'Marketing Snapshot'[CreateDate Year]
= MAX ( 'Marketing Snapshot'[CreateDate Year] )
&& 'Marketing Snapshot'[DateStampMonth]
= MAX ( 'Marketing Snapshot'[DateStampMonth] )
),
'Marketing Snapshot'[OpportunityOrigin] = "Marketing"
)
Note - this is ignoring a best-practice by using a FILTER( ALL()) on the fact table. If your table is small (which I assume it is from your earlier example), you will be okay. But if you try to apply this formula to a very large table you can have performance issues.
- gsed99
Looks like that opened the filter too wide (number is huge), and also changed the total, not just the line items.
I had changed the origional formula from MAX to = and entered values and that worked exactly, so something seems off with
the MAX.
Would this be best served as a measure of column? It's currently a measure.
- jmalone
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.
- gsed99
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.