1 Reply Latest reply on Nov 23, 2016 10:32 AM by Joe Oppelt

    Sum Measure between dynamic date range

    Jeff Roberts

      Each row represents an account.

      Each account's contract starts on a variable date.  The contract starts on the "2015 Contract Start Date" and ends on the "2015 Contract End Date".  Those dates are different for each account.

       

      I want to evaluate each account's performance (based on the sum of "Allergy Tests") during their own distinct contract year (ie between the "2015 Contract Start Date" and "2015 Contract End Date")

       

      I tried this:

      IF [Month End Date]>=[2015 Contract Start Date] AND

      [Month End Date]<= [2015 Contract End Date] THEN

      SUM([Allergen Tests])

      END

       

      It didnt work due to come aggregate and non-aggregate dimensions.  Any Ideas?  .twbx attached.