I need to calculate the variation of sales volume of the current month vs the same period of the previous year.
At a product level the formula is working fine, but the subtotals and totals DO NOT ADD UP.
I have tried many approaches and techniques available on the web, but I couldn't find anything related to calculating a measure using data from different periods.
The formula basically is:
[ (Sales Qty 2016) - (Sales Qty 2015) ] * [ (Sales Amount 2015) / (Sales Qty 2015) ]
My DAX formula attempt is:
=(CALCULATE(sumx(Sales_Data,Sales_Data[Qty]))- ****Sales Qty 2016******
CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey])))* ****Sales Qty 2015******
(CALCULATE(sumx(Sales_Data,Sales_Data[Net Sales Inc.Rebate]),SAMEPERIODLASTYEAR(Date_Table[DateKey])) / ******Sales Amount 2015******
CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey]))) ****Sales Qty 2015******
Any way that I can calculate the subtotal and totals a per the table below.
<colgroup><col span="5"></colgroup><tbody>
</tbody>
Many thanks.
Favio
At a product level the formula is working fine, but the subtotals and totals DO NOT ADD UP.
I have tried many approaches and techniques available on the web, but I couldn't find anything related to calculating a measure using data from different periods.
The formula basically is:
[ (Sales Qty 2016) - (Sales Qty 2015) ] * [ (Sales Amount 2015) / (Sales Qty 2015) ]
My DAX formula attempt is:
=(CALCULATE(sumx(Sales_Data,Sales_Data[Qty]))- ****Sales Qty 2016******
CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey])))* ****Sales Qty 2015******
(CALCULATE(sumx(Sales_Data,Sales_Data[Net Sales Inc.Rebate]),SAMEPERIODLASTYEAR(Date_Table[DateKey])) / ******Sales Amount 2015******
CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey]))) ****Sales Qty 2015******
Any way that I can calculate the subtotal and totals a per the table below.
Qty 2016 | Qty 2015 | ASP 2015 | Var Vol | |
Product A | 494 | 1,031 | $142.11 | -76,315 |
Product B | 10,888 | 7,706 | $428.29 | 1,362,814 |
Product C | 331 | 1,218 | $215.85 | -191,462 |
TOTAL | 11,720 | 9,961 | $372.43 | 655,112 |
Should be this total--> | 1,095,037 |
<colgroup><col span="5"></colgroup><tbody>
</tbody>
Many thanks.
Favio