Need DAX Help

[meena16_vibrant]

I used below measure to create average of 3 years of sales
AVG Sales =
var sales_2019=CALCULATE([salesvalue],'Date'[Month]=201812)
var sales_2020=CALCULATE([salesvalue],'Date'[Month]=201912)
var sales_2021=CALCULATE([salesvalue],'Date'[Month]=202012)
var avg_sales= (sales_2019+sales_2020+sales_2021)/3
return avg_sales

In the line chart I have to show comparison of dec 2021 sales vs avg sales in last 3 yrs

X axis - Dec month dates
Y axis - Sales and Avg sales

Avg sales trend is not showing in line chart but in card it shows. AVG values are blank.

I want to show dec sales vs avg sales(3yrs) for each dates in line chart. Could you please help me to get the DAX

[ppm1]

Here's a measure you can try instead. Note you can use the IN syntax to avoid replicate measures like you have for each year to improve performance.

Avg Past 3 Yrs =
VAR thisdate =
    MAX ( 'Date'[Date] )
VAR thismonth =
    MONTH ( thisdate )
VAR thisyear =
    YEAR ( thisdate )
RETURN
    CALCULATE (
        AVERAGEX ( DISTINCT ( 'Date'[Month] ), [Total Sales] ),
        //use your measure instead of [Total Sales]
        ALL ( 'Date' ),
        'Date'[Monthnumber] = thismonth,
        //use the Monthnumber and Year columns (whole numbers) in your Date table; create if needed
        'Date'[Year] >= thisyear - 3
            && 'Date'[Year] < thisyear
    )

Pat

[FreemanZ · Accepted solution]

hi   @meena16_vibrant 

try like:

 

AVG Sales =

var sales_2019=CALCULATE([salesvalue], DATEADD('Date'[Date], -3, YEAR))

var sales_2020=CALCULATE([salesvalue], DATEADD('Date'[Date], -2, YEAR))

var sales_2021=CALCULATE([salesvalue], DATEADD('Date'[Date], -1, YEAR))

var avg_sales= (sales_2019+sales_2020+sales_2021)/3

return avg_sales

[meena16_vibrant]

 It worked.Thanks for your help.