Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 sl...
Need Help
Show and Tell
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

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's avatar
wanda
Frequent Visitor
4 years ago

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