Calculating run rate

[Anonymous]

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

[v-gizhi-msft · Accepted solution]

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:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[wanda]

do you still have your original file ... can you send me it please?  thanks so much 😁

[edhans]

Hi @Anonymous - 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

[amitchandak]

@Anonymous 

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)