cancel
Showing results for
Did you mean:
Highlighted Helper I

## Calculating run rate

Hi everyone,

I need to find the runrate for every year in an area chart, it will be a constant number throughout the whole year.

The area chart will not be filtered by Month slicer, only Year slicer.

For example, to get the numbers for the

• 2020 runrate, take the sum of May-2019 till Apr-2020 (last 12 months including current month) and divided by 12
• 2019 runrate, take the sum of Jan-2019 till Dec-2019 and divided by 12 Does anyone know how I can get the numerator without being filtered by the month for the area chart?

Regards,

BK

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Calculating run rate

Hi,

I have an easier and high performance way to reach your requirement.

1)Create a date column first:

``Date = DATE('Table'[Year],'Table'[Month],1)``

2)Try this measure:

``````Measure =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR MaxYear =
CALCULATE ( MAX ( 'Table'[Year] ), ALL ( 'Table' ) )
RETURN
IF (
MAX ( 'Table'[Year] ) = MaxYear,
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date]
>= DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ) + 1, 1 )
&& 'Table'[Date] <= MaxDate
)
),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] IN FILTERS ( 'Table'[Year] ) )
)
)``````

The result shows: Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

3 REPLIES 3
Highlighted Super User IV

## Re: Calculating run rate

Formula assuming date table. But if you do have one, use column from your table

``````Measure =
var _m1 = calculate(sum(Table[Value]),filter(all(Date),Date[Year]=2019))
var _m2 = calculate(sum(Table[Value]),filter(all(Date),Date[date]>=Date(2019-05-01) && Date[date]<=Date(2020-04-30)))
Return
if(max(Table[Year])=2019,_m1, _m2)``````

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted Super User II

## Re: Calculating run rate

Hi @beekee - try the measure below. It will (should be) fully automatic for the future based on that logic. So:

1. If it is the last year of your data, it will expand to include the previous 12 months of data.
2. If it is in the a full year it will just average the value for the full 12 months of that year.
3. This has several variables
1. CurrentYear - gets the current year for the current record
2. CurrentYearMaxID - gets the maximum date ID for the current year. So now it is 12 for 2019, and 16 for 2020, but that will be changing as you grow your data.
3. StartDateID = Either starts at 1 if your DateID is <=12, or looks back 11 months before this one.
4. TotalValue - gets the average of the Values from the StartDateID record through the CurrentYearMaxID date record.

As long as your logic doesn't change, you should not have to touch this measure ever again. As long as your data set grows by one record a month and the dateID increments by 1 each month, this will work into the future.

EDIT: I want to thank @v-gizhi-msft for mentioning performance. My original measure was HORRIBLE in performance over large tables. I added 10,000 records to the dataset and mine was taking 33 seconds to refresh, vs sub-1 second times for @v-gizhi-msft's measure. I went back and took another look at it and really cleaned it up. Now it too is under 1 second for 10,000 records, and does NOT need a calculated column, which I try to avoid. See the bottom of my post for reasons why. Doesn't mean they aren't useful, but it is the last thing I want to pull out of my toolbag in the DAX model. It works on the existing data. The revised measure is below, and I've given @v-gizhi-msft appropriate Kudos for their "challenge." 😁 ``````Run Rate =
VAR CurrentYear = MAX('Table'[Year])
VAR MaxYear = MAX('Table'[Year])
VAR CurrentYearMaxID =
MAXX(
FILTER(
ALL('Table'[Date ID],'Table'[Year]),
'Table'[Year] = CurrentYear
),
'Table'[Date ID]
)
VAR StartDateID =
IF(
CurrentYearMaxID <= 12,
1,
CurrentYearMaxID - 11
)
VAR TotalValue =
IF(
CurrentYear <> MaxYear,
AVERAGEX(
FILTER(
ALLSELECTED('Table'[Date ID],'Table'[Value]),
'Table'[Date ID] >= StartDateID && 'Table'[Date ID] <= CurrentYearMaxID
),
'Table'[Value]
),
AVERAGEX(
FILTER(
ALLSELECTED('Table'),
'Table'[Year] = CurrentYear
),
'Table'[Value]
)
)
RETURN
TotalValue
``````

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted Community Support

## Re: Calculating run rate

Hi,

I have an easier and high performance way to reach your requirement.

1)Create a date column first:

``Date = DATE('Table'[Year],'Table'[Month],1)``

2)Try this measure:

``````Measure =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR MaxYear =
CALCULATE ( MAX ( 'Table'[Year] ), ALL ( 'Table' ) )
RETURN
IF (
MAX ( 'Table'[Year] ) = MaxYear,
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date]
>= DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ) + 1, 1 )
&& 'Table'[Date] <= MaxDate
)
),
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] IN FILTERS ( 'Table'[Year] ) )
)
)``````

The result shows: Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (710)