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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors