YOY Measure returning incorrect value in matrix with Client as context

[olimilo]

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

[danextian]

'AWF'[Year]

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

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

USE 

[olimilo]

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)

[v-hjannapu · Accepted solution]

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.

[v-hjannapu]

Hi @olimilo.,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[v-hjannapu]

Hi @olimilo,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @olimilo,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Community Support Team.

[Shahid12523]

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)