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
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
    Icon for Resolver II rankResolver II
    5 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? 

    • erikriesenberg's avatar
      erikriesenberg
      Frequent Visitor
      4 years ago

      Yes, I Returned __PREV_YEAR and get 233.

      • erikriesenberg's avatar
        erikriesenberg
        Frequent Visitor
        4 years ago

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