Forum Discussion

erikriesenberg's avatar
erikriesenberg
Frequent Visitor
5 years ago

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],
            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?
Need Help

7 Replies

  • sm_talha's avatar
    sm_talha
    Resolver II
    5 years ago

    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)
    )
    • erikriesenberg's avatar
      erikriesenberg
      Frequent Visitor
      5 years ago

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

      • erikriesenberg's avatar
        erikriesenberg
        Frequent Visitor
        5 years ago

        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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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