Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a table like:
I need to add using dax, an column with average of last 2 year like this:
Do someone know if this it possible to do?
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
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]
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
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]
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |