Trailing 12 Month Average

[Anonymous]

Given this data:

What would be the best way to create a trailing 12 month average for company retention? Can I do it within this table or should I create a date table and calculate it from there?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please check out the demo in the attachment. It could be the result you want.

1. Create a date table. 

2. Don't establish any relationships.

3. I would suggest you create a middle table. You also can try a measure which would be slow.

MiddleTable =
SUMMARIZE (
    'Calendar',
    'Calendar'[Date].[Year],
    'Calendar'[Date].[Month],
    "amount", CALCULATE (
        COUNT ( Table1[co] ),
        FILTER (
            'Table1',
            'Table1'[startDate] <= MIN ( 'Calendar'[Date] )
                && 'Table1'[endDate] >= MAX ( 'Calendar'[Date] )
        )
    )
)

Or 

Measure 3 =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Calendar',
            'Calendar'[Date].[Year],
            'Calendar'[Date].[Month],
            "amount", CALCULATE (
                COUNT ( Table1[co] ),
                FILTER (
                    'Table1',
                    'Table1'[startDate] <= MIN ( 'Calendar'[Date] )
                        && 'Table1'[endDate] >= MAX ( 'Calendar'[Date] )
                )
            )
        ),
        [amount]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] >= MIN ( 'Calendar'[Date] )
            && 'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), 11 )
    )
)

Best Regards,

Dale

[Greg_Deckler]

Seems like there is some data missing. But, you can take a look at my Rolling Months Quick Measure here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490