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 ...
  • 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

     

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