topic Need DAX Help in DAX Commands and Tips

Need DAX Help

meena16_vibrant — Thu, 22 Dec 2022 11:54:45 GMT

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

Re: Need DAX Help

FreemanZ — Thu, 22 Dec 2022 12:27:53 GMT

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

Re: Need DAX Help

meena16_vibrant — Thu, 22 Dec 2022 13:11:11 GMT

It worked.Thanks for your help.

Re: Need DAX Help

ppm1 — Thu, 22 Dec 2022 13:14:48 GMT

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