Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
v-eqin-msft
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.