cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rick_ferreira
Helper II
Helper II

Build a column with average of last 2 year

Hi all,

I have a table like:

Rick_ferreira_0-1618738413379.png

 



I need to add using dax, an column with average of last 2 year like this:

Rick_ferreira_0-1618738332401.png


Do someone know if this it possible to do?

Thanks

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @Rick_ferreira ,

 

I believe that being an average you would be better making a measure however try the following column:

 

Average_last_ 2_year = 
VAR year_ref =
    YEAR ( 'Table'[Date] )
VAR month_ref =
    MONTH ( 'Table'[Date] )
RETURN
    IF (
        year_ref - 2
            >= MINX ( ALL ( 'Table'[Date] ), YEAR ( 'Table'[Date] ) ),
        AVERAGEX (
            FILTER (
                ALL ( 'Table' ),
                YEAR ( 'Table'[Date] ) < year_ref
                    && YEAR ( 'Table'[Date] ) >= year_ref - 2
                    && MONTH ( 'Table'[Date] ) = month_ref
            ),
            'Table'[Value]
        )
    )

 

 

MFelix_0-1618828870565.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User III
Super User III

Hi @Rick_ferreira ,

 

I believe that being an average you would be better making a measure however try the following column:

 

Average_last_ 2_year = 
VAR year_ref =
    YEAR ( 'Table'[Date] )
VAR month_ref =
    MONTH ( 'Table'[Date] )
RETURN
    IF (
        year_ref - 2
            >= MINX ( ALL ( 'Table'[Date] ), YEAR ( 'Table'[Date] ) ),
        AVERAGEX (
            FILTER (
                ALL ( 'Table' ),
                YEAR ( 'Table'[Date] ) < year_ref
                    && YEAR ( 'Table'[Date] ) >= year_ref - 2
                    && MONTH ( 'Table'[Date] ) = month_ref
            ),
            'Table'[Value]
        )
    )

 

 

MFelix_0-1618828870565.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors