Forum Discussion

ScottTynan's avatar
ScottTynan
Frequent Visitor
4 years ago
Solved

Finding the % difference in sales for a product compared to average of the last two years

Hello, 

I'm hoping someone will be able to shed some light on this for me. I posted a question in the DAX forum but haven't had any luck with an answer. I'm hoping someone might be monitoring this forum who can point me in the right direction?

This is a link to the original post 
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Finding-the-difference-in-sales-for-a-product-compare-to-average/m-p/2674678/highlight/true#M79867

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi ScottTynan 

     

    Try using ALLEXCEPT function in measures. 

    Average of Last 2 Years = 
VAR _lastYearRevenue = CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),DATEADD('Revenue by Period'[Date],-1,Year),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code]))
VAR _last2YearRevenue = CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),DATEADD('Revenue by Period'[Date],-2,Year),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code]))
RETURN
DIVIDE(_lastYearRevenue + _last2YearRevenue, 2)

    % Difference = DIVIDE(CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code])) - [Average of Last 2 Years], [Average of Last 2 Years])

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi ScottTynan 

     

    Try using ALLEXCEPT function in measures. 

    Average of Last 2 Years = 
VAR _lastYearRevenue = CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),DATEADD('Revenue by Period'[Date],-1,Year),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code]))
VAR _last2YearRevenue = CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),DATEADD('Revenue by Period'[Date],-2,Year),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code]))
RETURN
DIVIDE(_lastYearRevenue + _last2YearRevenue, 2)

    % Difference = DIVIDE(CALCULATE(SUM('Revenue by Period'[Revenue NZ$]),ALLEXCEPT('Revenue by Period','Revenue by Period'[Account Number],'Revenue by Period'[Date],'Revenue by Period'[Job Code])) - [Average of Last 2 Years], [Average of Last 2 Years])

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.