Forum Discussion

Helper III

8 years ago

Solved

Count 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...

8 years ago

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

Resolver III

8 years ago

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

gsed99
Helper III
8 years ago
Thank you!
- jmalone
  Resolver III
  8 years ago
  You are welcome! :smileyvery-happy:
  - gsed99
    Helper III
    8 years ago
    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]).

Related content