Calculate Average for a measure

[yashrajs]

I have a table that looks something like this:

I use the following formula to caluculate the MEASURE 'Alternative Claculated Burn Rate':

Alternative Calculated Burn Rate = DIVIDE([Count of Contracts], [Sum of Total Time])

Plase note that here Count of Contracts and Sum of Total Time are also measures. 

My goal is to create another measure that will hold a single scalar value and will be the average of 'Alternative Calculated Burn Rate' for the last six months. I don't want to have this average for every month, I just want one average calculated using the latest six months. 

I have already tried using :

Alternative Burn Rate MA = CALCULATE(SUMX(

        TOPN(

            6,

            'Historical Contracts by Load Date',

            'Historical Contracts by Load Date'[Start of Month Date], DESC

            ),

            [Alternative Calculated Burn Rate]

        )/6

    )

This doesn't seem to work correctly. 

All help is appreciated, thank you in advance!

[v-xinruzhu-msft · Accepted solution]

Hi @yashrajs 

You can consider to create a date table, then create one-many relationship between tables

Then use the following measure.

Measure =
CALCULATE (
    AVERAGE ( 'Historical Contracts by Load Date'[Alternative Calculated Burn Rate] ),
    DATESINPERIOD ( 'DateTable'[Date], MAX ( 'DateTable'[Date] ), -6, MONTH )
)

and you can refer to the following link about the function.

DATESINPERIOD function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xinruzhu-msft]

Hi @yashrajs 

Try the following measure.

Alternative Burn Rate MA =
CALCULATE (
    DIVIDE (
        SUMX (
            TOPN (
                6,
                ALLSELECTED ( 'Historical Contracts by Load Date' ),
                'Historical Contracts by Load Date'[Start of Month Date], DESC
            ),
            [Alternative Calculated Burn Rate]
        ),
        6
    )
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yashrajs]

Thanks for the suggestion @v-xinruzhu-msft  however this doesn't seem to work correctly. It gives an insanely high value.