cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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

 

runrate.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

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
Community Support

Re: Calculating run rate

Hi,

 

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

Please take following steps:

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:

21.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Calculating run rate

@beekee 

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)

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
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." 😁

 

2020-04-12 21_08_18-Untitled - Power BI Desktop.png

 

 

 

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 I answer your question? Mark my post as a solution!
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
Community Support

Re: Calculating run rate

Hi,

 

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

Please take following steps:

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:

21.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

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

Using the Community

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors