cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erikriesenberg
Frequent Visitor

YoY% change calculation not returning correct values

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.
erikriesenberg_0-1631042098087.png

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?
7 REPLIES 7
Eyelyn9
Community Support
Community Support

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
Resolver II
Resolver II

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)
)

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

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.

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

Yes, I Returned __PREV_YEAR and get 233.

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)
)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.