Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX YoY (absolute/percentage) difference

Hello Folks, I need help in YoY calculation.   I have monthly sales data from Jan2018 -July2020. I want to Calculate YoY  a. absolute difference b. Percentage difference  for each month say ...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hello @jay5300

    Try something like this:

    % Change = 
VAR varThisYear = [Sum of Amount]
VAR varLastYear = 
    CALCULATE(
        [Sum of Amount] ,
        FILTER(
                ALL('Table1'[FiscalYear]),
                'Table1'[FiscalYear] = MAX('Table1'[FiscalYear]) - 1)
                )
RETURN DIVIDE(varThisYear - varLastYear,varLastYear)

    Pbix joined.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Amit, 

 

Thanks once again for responding to my query. I have used calculations you provided as reference for creating.

Current year (CY) and Previous Year (PY).

 

CY= CALCULATE(SUM(Month_Data[Month.CUST_COUNT]),DATESMTD(DATEADD('Dates'[Date], -1, MONTH)))

PY = CALCULATE(SUM(Month_Data[Month.CUST_COUNT]),DATESMTD(DATEADD('Dates'[Date], -13, MONTH)))

diff % = divide([CY]-[PY],[CY])
 
CY and PY fields retrieves the values but yoy is not calculated and is shown as 100%. (please see tbe image for reference).
Also, i want the YoY diff % to be calculated for all months where previous year data exists.
Is it possible.? can you please guide.?

 

I 

v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

Hello @jay5300

Try something like this:

% Change = 
VAR varThisYear = [Sum of Amount]
VAR varLastYear = 
    CALCULATE(
        [Sum of Amount] ,
        FILTER(
                ALL('Table1'[FiscalYear]),
                'Table1'[FiscalYear] = MAX('Table1'[FiscalYear]) - 1)
                )
RETURN DIVIDE(varThisYear - varLastYear,varLastYear)

Pbix joined.