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('...
Need Help
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.
      • sm_talha's avatar
        sm_talha
        Resolver II
        4 years ago

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