Applying a growth percentage to a measure result

[mikemagill]

I have a data table that has the following fields:

• Date
• LocationID
• EventID
• Count

I 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:

• Date
• LocationID
• EventID
• Target%

This table has multiple records for each LocationID/EventID.  For example:

01/01/17, LDN, AM, 10%

01/01/18, LDN, AM, 8% 

In this example, the growth rate is 10% from 01/01/17 to 31/12/17 and then reduces to 8%.

I want to work out a measure that multiplies the CountLastYr by the relevant growth percentage for that LocationID, EventID and date.

Can anyone help?

[mikemagill]

I'm sorry those measures throw up errors for me.  I've added an expected result column to my data that shows the percentage target that I expect to appear against each count.  This is what I would use to add to the Count LastYr measure.

Updated sample data

[mikemagill]

Thanks for the input but that's not the results I'm looking for.

I want my count table to show, for every date that there is a count, the CountLastYr * (1 + Target).  The target percentage being the latest target set for that particular count type, location and event type.

[v-frfei-msft]

Hi @mikemagill,

To create the measures as below. If it doesn't meet your requirement, kindly share your excepted result to me.

tar = var ma = CALCULATE(MAX('Date Table'[Date]),ALLEXCEPT('Date Table','Date Table'[LocationId],'Date Table'[EventNameId],'Date Table'[CountType]))
return
CALCULATE(MAX('Date Table'[Target]),FILTER('Date Table','Date Table'[Date]=ma))

Measure = 
VAR da =
    MAX ( 'Date Table'[Date] )

RETURN
    CALCULATE (
        SUM ( 'Total Attendance'[Count] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] >= da - 364 && 'Table'[Date] <= da )
    )
        * 1+MAXX(ALLEXCEPT('Date Table','Date Table'[CountType],'Date Table'[EventNameId],'Date Table'[LocationId]),[tar])

Regards,

Frank

[mikemagill]

Here is a link to a spreadsheet containing two tables; CountData and Targets.

Sample Data

There should be sufficient data in there to demonstrate what I want to achieve.

[v-frfei-msft]

Hi @mikemagill,

Please check the folloing steps as below. If it doesn't meet your requirement, kindly share your excepted result to me.

1. Create a date table and create relationships between tables.

Table = CALENDARAUTO()

2. Create a measure as below.

Measure = 
VAR da =
    MAX ( 'Date Table'[Date] )
VAR tar =
    MAX ( 'Date Table'[Target] )
RETURN
    CALCULATE (
        SUM ( 'Total Attendance'[Count] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] >= da - 364 && 'Table'[Date] <= da )
    )
        * tar

Regards,

Frank

[v-frfei-msft]

Hi @mikemagill,

Could you please share your sample data or pbix to me? You can upload your file to onedrive and share the link here.

Regards,

Frank