Forum Discussion
Solved
Average Line per Month
I am creating a graph of the applications that have been received each month for the past 3 years. What I ma needing to do is to create a 2 year average line that would show an estimated average ...
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Sales measure: = SUM( sales[sales] )WINDOW function (DAX) - DAX | Microsoft Learn
rolling 24 months avg sales measure: = VAR _t = WINDOW ( -24, REL, -1, REL, ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ), ORDERBY ( 'calendar'[Month Year sort], ASC ) ) RETURN IF ( COUNTROWS ( _t ) = 24 && SUM ( sales[sales] ), AVERAGEX ( WINDOW ( -24, REL, -1, REL, ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ), ORDERBY ( 'calendar'[Month Year sort], ASC ) ), CALCULATE ( SUM ( sales[sales] ) ) ) )
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Sales measure: =
SUM( sales[sales] )
WINDOW function (DAX) - DAX | Microsoft Learn
rolling 24 months avg sales measure: =
VAR _t =
WINDOW (
-24,
REL,
-1,
REL,
ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ),
ORDERBY ( 'calendar'[Month Year sort], ASC )
)
RETURN
IF (
COUNTROWS ( _t ) = 24
&& SUM ( sales[sales] ),
AVERAGEX (
WINDOW (
-24,
REL,
-1,
REL,
ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ),
ORDERBY ( 'calendar'[Month Year sort], ASC )
),
CALCULATE ( SUM ( sales[sales] ) )
)
)
This is great thank you, it worked when using the COUNTROWS function instead of SUM