Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

YOY Measure returning incorrect value in matrix with Client as context

I'm trying to get the YOY of two metrics based on the date and the client. When I plot them in a table with only the Year as the context, I get the correct value:

 

However, when I plot this on a matrix with the Client as the context, the YOY values return incorrect values:

 


This is how I am computing for the YOY AU. YOY MD is the SUM of the MD column instead of COUNTROWS. Am I missing a row context here that's why the measure is returning an incorrect value?

YOY AU = 
    VAR _year = MAX('AWF'[Year])
    VAR _currval = COUNTROWS('AWF')
    VAR _prevval = 
        CALCULATE(
            COUNTROWS('AUWF'),
            'AWF'[Year] = _year - 1
        )
    RETURN
    CALCULATE(DIVIDE(_currval - _prevval, _prevval))

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello olimilo,
    Thank you for reaching out to the Microsoft fabric community forum. 

    I tested your scenario in Power BI Desktop with sample data, and I was able to reproduce the issue you described where the YOY measure returned incorrect values when Client was added to the matrix.

    The root cause is that the original measure was not correctly maintaining the Client context when retrieving the prior-year values. After adjusting the DAX, the YOY values now calculate as expected both at the Year level and the Year + Client level.

    Here are the corrected measures:

    Total AU = SUM(AWF[AU])

Total MD = SUM(AWF[MD])



YOY AU =

VAR _year = MAX(AWF[Year])

VAR _currval = [Total AU]

VAR _prevval =

   CALCULATE (

       [Total AU],

       FILTER (

           ALL (AWF),

           AWF[Year] = _year - 1

               && AWF[Client] = MAX(AWF[Client])

       )

   )

RETURN

DIVIDE (_currval - _prevval, _prevval)



YOY MD =

VAR _year = MAX(AWF[Year])

VAR _currval = [Total MD]

VAR _prevval =

   CALCULATE (

       [Total MD],

       FILTER (

           ALL (AWF),

           AWF[Year] = _year - 1

               && AWF[Client] = MAX(AWF[Client])

       )

   )

RETURN

DIVIDE (_currval - _prevval, _prevval)

    With these measures, the matrix now returns the correct YOY values per Client and per Year.

    For example, with the sample dataset:

    For your reference, I am attaching the .pbix file that demonstrates the corrected implementation.

    Best regards,
    Harshitha jannapu.

7 Replies

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Your YOY AU measure breaks when you add Client to the matrix because _prevval doesn’t inherit the Client filter. To fix it, update your measure like this

     

     

    YOY AU =
    VAR _year = MAX('AWF'[Year])
    VAR _currval = COUNTROWS('AWF')
    VAR _prevval =
    CALCULATE(
    COUNTROWS('AWF'),
    'AWF'[Year] = _year - 1,
    ALL('AWF'[Year]) -- Removes year filter, keeps Client
    )
    RETURN
    DIVIDE(_currval - _prevval, _prevval)

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago
    'AWF'[Year]

    This only sees the current row and not the whole table. Try

    FILTER ( ALL ( 'AWF'[Year] ), 'AWF'[Year] = _year -1 )

    USE 

    • olimilo's avatar
      olimilo
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      It's still not quite returning the correct value:

       

      Like for row 1, I am expecting -40.74% and -40.87%.

       

      ~YOY AU = 
    VAR _year = MAX('AWF'[Year])
    VAR _currval = COUNTROWS('AWF')
    VAR _prevval = 
        CALCULATE(
            COUNTROWS('AWF'),
            FILTER(ALL('AWF'[Year]), 'AWF'[Year] = _year - 1)
        )
    RETURN
    DIVIDE(_currval - _prevval, _prevval)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hello olimilo,
        Thank you for reaching out to the Microsoft fabric community forum. 

        I tested your scenario in Power BI Desktop with sample data, and I was able to reproduce the issue you described where the YOY measure returned incorrect values when Client was added to the matrix.

        The root cause is that the original measure was not correctly maintaining the Client context when retrieving the prior-year values. After adjusting the DAX, the YOY values now calculate as expected both at the Year level and the Year + Client level.

        Here are the corrected measures:

        Total AU = SUM(AWF[AU])

Total MD = SUM(AWF[MD])



YOY AU =

VAR _year = MAX(AWF[Year])

VAR _currval = [Total AU]

VAR _prevval =

   CALCULATE (

       [Total AU],

       FILTER (

           ALL (AWF),

           AWF[Year] = _year - 1

               && AWF[Client] = MAX(AWF[Client])

       )

   )

RETURN

DIVIDE (_currval - _prevval, _prevval)



YOY MD =

VAR _year = MAX(AWF[Year])

VAR _currval = [Total MD]

VAR _prevval =

   CALCULATE (

       [Total MD],

       FILTER (

           ALL (AWF),

           AWF[Year] = _year - 1

               && AWF[Client] = MAX(AWF[Client])

       )

   )

RETURN

DIVIDE (_currval - _prevval, _prevval)

        With these measures, the matrix now returns the correct YOY values per Client and per Year.

        For example, with the sample dataset:

        For your reference, I am attaching the .pbix file that demonstrates the corrected implementation.

        Best regards,
        Harshitha jannapu.