Calculate average cost for not full period

[JānisB]

I have a measure:

Rolling 12M = CALCULATE (
[Period cost]/12,
DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -12, MONTH )
)

which calculates average cost.

How to change the code, if there is no data for last 12 months and how to calculate average cost for not full period?

For example, the data starts from 01-2024. It is necessary to calculate average on the end of 07-2024 divided by 7. If there is data for last 12 month, then it is necessary to divide by 12.

[OwenAuger · Accepted solution]

Hi @JānisB 

Would it be sufficient to exclude months where [Period cost] is blank from the average calculation?

If so, you could use AVERAGEX which automatically excludes blank values from the average.

You would need to have a Month/Year or similar column in your 'Calendar' table for this:

Rolling 12M =
CALCULATE (
    AVERAGEX ( VALUES ( 'Calendar'[Year/Month] ), [Period cost] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -12, MONTH )
)

This measure would return the average of nonblank values of [Period cost] evaluated over the 12-month period ending at MAX ( 'Calendar'[Date] ).

Does this or something similar work for you?