Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Trailing 12 Month Average

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?

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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