cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors