YoY% change calculation not returning correct values

[erikriesenberg]

I have a calculation returning the total delivery fees we've collected divided by the number of enrollments during any given period of time.  

Delivery Fees Per Enrollment =

DIVIDE(

    SUM('VDP Enrollments'[Delivery Fees]),

    COUNTA('VDP Enrollments'[Class])

)

Here are the correct values this formula returns for last year and this year.

I tried creating a YoY % change calculation, but it's not returning accurate numbers.

Here's the formula for YoY % change:
 

Delivery Fees Per Enrollment YoY% =

IF(

    ISFILTERED('Dates'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            [Delivery Fees Per Enrollment],

            DATEADD('Dates'[Date].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE([Delivery Fees Per Enrollment] - __PREV_YEAR, __PREV_YEAR)

)

When I use this formula and use a slicer to look at say June for example, it returns a value of 4.24%, but if you look at the values for June in 2020 and 2021 you can easily see that the correct value should be 6.86% ((249-233)/233).

Where am I going wrong?

[v-eqin-msft]

Hi @erikriesenberg ,

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[sm_talha]

When calculating YoY% of "Averages", we usually have this problem because the DAX try to calculate average for each section. Try creating a variable for Average and then see if it works:

Delivery Fees Per Enrollment YoY% =
VAR _average = [Delivery Fees Per Enrollment]

IF(

    ISFILTERED('Dates'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            _average,

            DATEADD('Dates'[Date].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)

)

[erikriesenberg]

I tried that, but I'm receiving the following error message:
The syntax for 'IF' is incorrect. (DAX(VAR _average = [Delivery Fees Per Enrollment]IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( _average, DATEADD('Dates'[Date].[Date], -1, YEAR) ) RETURN DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)))).

[erikriesenberg]

I found the error in the DAX, this is the new DAX formula:

Delivery Fees Per Enrollment YoY% =

VAR _average = [Delivery Fees Per Enrollment]

RETURN

IF(

ISFILTERED('Dates'[Date]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_YEAR =

CALCULATE(

_average,

DATEADD('Dates'[Date].[Date], -1, YEAR)

)

RETURN

DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)

)

However, this still returns the wrong value.

[sm_talha]

Can you try Returning the __PREV_YEAR variable from your orginal formula and see if it is 233 for the month in discussion? 

[erikriesenberg]

Yes, I Returned __PREV_YEAR and get 233.

[erikriesenberg]

I think the problem that I have is that the [Delivery Fees Per Enrollment] calculation isn't year specific.

Delivery Fees Per Enrollment =

DIVIDE(

    SUM('VDP Enrollments'[Delivery Fees]),

    COUNTA('VDP Enrollments'[Class])

)

So when it tries to run this part of the DAX
DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)

the -average is pulling the Delivery Fees for all years

I tried adding a variable for __CURR_YEAR, but that doesn't seem to have fixed it.

Delivery Fees Per Enrollment YoY% =

IF(

    ISFILTERED('Dates'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __CURR_YEAR =

        CALCULATE(

            [Delivery Fees Per Enrollment],

            DATEADD('Dates'[Date].[Date], 0, YEAR)

)

VAR __PREV_YEAR =

        CALCULATE(

            [Delivery Fees Per Enrollment],

            DATEADD('Dates'[Date].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(__CURR_YEAR - __PREV_YEAR, __PREV_YEAR)

)