I have a data table that has the following fields:

  • LocationID
  • EventID
  • CountType
  • Date
  • Count

This table shows a list of attendance counts on various dates at different locations and different events.

I already have a measure that calculates the equivalent count last year:

  • CALCULATE(sum('Total Attendance'[Count]),DATEADD('Date Table'[Date],-364,DAY))

I have a table of growth targets that has the following fields:

  • LocationID
  • EventID
  • EventType
  • Date
  • Target%

This table has a record for each time a target changes.

I want to work out a measure that identifies the applicable growth target% for a particular count on a particular date in the data table so that I can multiply this by the CountLastYr.

Here is some sample data that also includes a column showing the expected result for comparison:

Sample Data.xlsx

Can anyone help produce a measure that does what I need?