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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-gizhi-msft
Community Support
Community Support

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

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

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

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

edhans
Super User
Super User

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." 😁

 

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
amitchandak
Super User
Super User

@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)

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.