Forum Discussion

gsed99's avatar
gsed99
Icon for Helper III rankHelper 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...
Need Help
  • jmalone's avatar
    jmalone
    8 years ago
    Current Year SAO Records :=
CALCULATE (
    COUNT ( 'TableName'[SAO Date] ),
    FILTER (
        ALL ( 'TableName'[SAO Year] ),
        'TableName'[SAO Year] = MAX ( 'TableName'[SAO Year] )
    )
)
jmalone's avatar
jmalone
Icon for Resolver III rankResolver 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's avatar
gsed99
Icon for Helper III rankHelper III
8 years ago

Thank you!

  • jmalone's avatar
    jmalone
    Icon for Resolver III rankResolver III
    8 years ago

    You are welcome! :smileyvery-happy:

    • gsed99's avatar
      gsed99
      Icon for Helper III rankHelper 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]).

      • gsed99's avatar
        gsed99
        Icon for Helper III rankHelper III
        8 years ago

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