Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Applying a growth percentage to a measure result

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?

6 REPLIES 6
Frequent Visitor

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

Frequent Visitor

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.

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

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.

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors