cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

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],
)
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
Community Support

Best Regards,
Eyelyn Qin

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,
)
RETURN
DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)
)
Frequent Visitor

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

Frequent Visitor

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,
)
RETURN
DIVIDE( _average - __PREV_YEAR, __PREV_YEAR)
)

However, this still returns the wrong value.
Resolver II

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

Frequent Visitor

Yes, I Returned __PREV_YEAR and get 233.

Frequent Visitor

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],
)
VAR __PREV_YEAR =
CALCULATE(
[Delivery Fees Per Enrollment],
)
RETURN
DIVIDE(__CURR_YEAR - __PREV_YEAR, __PREV_YEAR)
)

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors