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

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

        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.

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

      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?