Build column with average of last 12 month

[Anonymous]

Hi all,
I have a table below:

I need do create a column, using dax with avarage of the last 12 months, for instance:

Basically, the avg column is the avarage of the last 12 month.

Can someone help me?
Thanks

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

If there is a date table in your model, you can try this Calculated column.

Avrage_last_12_month =

CALCULATE (

    AVERAGE ( 'Table'[value] ),

    DATESINPERIOD ( 'calendar'[Date], 'Table'[Date], -12, MONTH ),

    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )

)

If you don't have date table, you can try this Calculated column.

Avg last 12 months =

VAR day =

    YEAR (

        CALCULATE (

            SELECTEDVALUE ( 'Table'[Date] ),

            FILTER ( 'Table', 'Table'[Date] = MIN ( 'Table'[Date] ) )

        )

    )

VAR month_count =

    IF (

        'Table'[Date] >= DATE ( day, 12, 31 ),

        12,

        CALCULATE (

            COUNT ( 'Table'[Date] ),

            FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )

        )

    )

VAR cur_month =

    MONTH ( 'Table'[Date] )

VAR cur_year =

    YEAR ( 'Table'[Date] )

VAR sum_last_12_month =

    IF (

        cur_month < 12,

        CALCULATE (

            SUM ( 'Table'[value] ),

            FILTER (

                ALL ( 'Table' ),

                (

                    YEAR ( 'Table'[Date] ) = cur_year

                        && MONTH ( 'Table'[Date] ) <= cur_month

                )

                    || (

                        YEAR ( 'Table'[Date] ) = cur_year - 1

                            && MONTH ( 'Table'[Date] ) > cur_month

                    )

            )

        ),

        CALCULATE (

            SUM ( 'Table'[value] ),

            FILTER ( ALL ( 'Table' ), YEAR ( 'Table'[Date] ) = cur_year )

        )

    )

RETURN

    sum_last_12_month/month_count

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

Check out this article from SQLBI

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

This would help to solve your problem

Regards,

Sayali

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

[Anonymous]

Thanks

I need to create a column on table not measure.
This article shows measure.