Forum Discussion

meena16_vibrant's avatar
meena16_vibrant
Frequent Visitor
3 years ago
Solved

Need DAX Help

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

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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

     

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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