Count formula with filter for Max Year

[gsed99]

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 SAO Year are separate columns/data points.

I think I am close but, I am new to Power BI, so looking for some guidance.
Thank you,

Greg

[jmalone · Accepted solution]

Current Year SAO Records :=
CALCULATE (
    COUNT ( 'TableName'[SAO Date] ),
    FILTER (
        ALL ( 'TableName'[SAO Year] ),
        'TableName'[SAO Year] = MAX ( 'TableName'[SAO Year] )
    )
)

[gsed99]

Thank you!

[jmalone]

You are welcome! 

[gsed99]

@jmalone, thank you for all the help! I have used these metrics in a new report, which I added drill down options, and found that some records with prior year dates (2017 and older) are making their way into the data. What is strange is that the totals match what the correct numbers are, but when adding a drill down like Marketing Tactic, the line items for the tactics show numbers that shold not be shown based on the filter for the measure, as the filter should only show records from 2018. This also means the rows will add up to be more than the total, as older records are showing, and the total is correct for 2018.
Do you know how I can fix this?

Example:

	# CY MQL
Inbound	35
Outbound	45
(BLANK)	3
Total	80

The total of 80 is totaling correct, and the inbound/outbound numbers are good, but the (BLANK) shouldnt be there, and when I dig into those records, they were not created in 2018, which is the filter the metric is based on, MAX([MQL Year]).

[jmalone]

Usually you see (Blank) values when you have records with an [MQL Date] but no data in the [Marketing Tactic] column. The measure is counting the number of values in the [MQL Date] column. The result you see is those three values. You are sure those three records have an [MQL Date] = 2018?

[gsed99]

I 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] )
))

[jmalone]

Sorry 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]

@jmalone

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 

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.

[jmalone]

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.

[gsed99]

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

[jmalone]

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

[gsed99]

Correct, its all one table

[gsed99]

@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?