Calculate YoY Change if applied a Year filter

[charonT]

Hi I am writing a DAX to calculate a YoY Change. Since the year is applied as a filter, when I select the current year, the power bi will not return the data from the year before. Here is the DAX I am working with:

YoY_change =

VAR CurYear = SELECTEDVALUE('Year'[Year])

VAR PO_CurYear =

    DIVIDE(

        SUMX(

            FILTER('PO', 'PO'[Year] = CurYear),

            'PO'[Numerator]

        ),

        SUMX(

            FILTER('PO', 'PO'[Year] = CurYear),

            'PO'[Denominator]

        )

    )

VAR PreYear = CurYear - 1

VAR PO_PreYear =

    DIVIDE(

        calculate(SUM('PO'[Numerator]),

            FILTER(All('PO'[Year]), 'PO'[Year] = PreYear)            

        ),

        calculate(SUM('PO'[Denominator]),

            FILTER('PO', 'PO'[Year] = PreYear)            

        )

    )

RETURN

    PO_CurYear - PO_PreYear

[charonT]

Thank you, I resolved it by myself in the end.

Following a similar approach to the reply, I added two columns, "last_num" and "last_den," and used VLOOKUP to add the data from the previous year into these columns.

While this is not the ideal solution as I intended to maintain the data structure and model as simple as possible, it still serves as a resolution.

[Anonymous · Accepted solution]

Hi @charonT ,

I suggest that you create a new table (without any relationship to the original table) with only the years that need to be selected, and then use this new table to create the slicer. And use "ALLSELECTED" in your DAX.
Since you didn't provide the data, I can only provide you with an example of how to implement it.
Here is my sample data:

I add a new table for creating a slicer for year:

Then I use these DAXs to create measures:

CurrentYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)

PreviousYear = 
CALCULATE(
    SUM(Sheet16[sales]),
    YEAR('Sheet16'[date]) = SELECTEDVALUE(Slicer[Year]) - 1 && 'Sheet16'[weeknumber] = SELECTEDVALUE(Sheet16[weeknumber]) && 'Sheet16'[country] = SELECTEDVALUE(Sheet16[country]),
    ALLSELECTED(Sheet16)
)

The final output is like below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.