Forum Discussion
gsed99
Helper III
Helper IIICount formula with filter for Max Year
Hello, I am trying to do a count on # of records, but based on a filter for the year. What I essentialy want to say is count # of SAO Date when SAO Year=MAX (meaning current year). Both SAO Date and...
Current Year SAO Records := CALCULATE ( COUNT ( 'TableName'[SAO Date] ), FILTER ( ALL ( 'TableName'[SAO Year] ), 'TableName'[SAO Year] = MAX ( 'TableName'[SAO Year] ) ) )
gsed99Helper III
Helper IIIThanks, 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
Resolver III
Resolver IIIYou 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.