Calculate Prior Year Values

[MichaelZang]

Hi Community,

I hope you can help me with this issue - thanks in advance! 

Context: I want to calculate the % difference in values between 2 years when we select one year in the slicer

• For example: If I select 2024, it will calculate the % difference between 2024 and 2023 (with the selected Period)

My DAX code so far (cannot display Prior Year values as attached image):

UOM_Selected_Year = CALCULATE(

    SELECTEDVALUE(financial[UOM %]),

    FILTER(dim_time, dim_time[year] = SELECTEDVALUE(dim_time[year]) &&

                        (dim_time[period] = "FY" || dim_time[period] = "H1")),

    FILTER(dim_region, dim_region[region_name] = "Global")

)

UOM_Prior_Year =

VAR SelectedYear = SELECTEDVALUE(dim_time[year])

VAR PriorYear = SelectedYear - 1

RETURN CALCULATE(

    SELECTEDVALUE(financial[UOM %]),

    FILTER(dim_time, dim_time[year] = PriorYear &&

                        (dim_time[period] = "FY" || dim_time[period] = "H1")),

    FILTER(dim_region, dim_region[region_name] = "Global")

)

UOM_difference =

VAR SelectedYear = SELECTEDVALUE(dim_time[year])

VAR PriorYear = SelectedYear - 1

VAR UOM_Selected_Year = CALCULATE(

    SELECTEDVALUE(financial[UOM %]),

    FILTER(dim_time, dim_time[year] = SelectedYear &&

                        (dim_time[period] = "FY" || dim_time[period] = "H1")),

    FILTER(dim_region, dim_region[region_name] = "Global")

)

VAR Prior_Year = CALCULATE(

    SELECTEDVALUE(financial[UOM %]),

    FILTER(dim_time, dim_time[year] = PriorYear &&

                        (dim_time[period] = "FY" || dim_time[period] = "H1")),

    FILTER(dim_region, dim_region[region_name] = "Global")

)

RETURN IF(NOT ISBLANK(UOM_Selected_Year) && NOT ISBLANK(Prior_Year),

        (UOM_Selected_Year - Prior_Year) * 100,

        BLANK()

    )

My PBIX file: HERE

[bhanu_gautam]

@MichaelZang , Try using

dax
UOM_Selected_Year =
CALCULATE(
SELECTEDVALUE(financial[UOM %]),
FILTER(dim_time, dim_time[year] = SELECTEDVALUE(dim_time[year]) &&
(dim_time[period] = "FY" || dim_time[period] = "H1")),
FILTER(dim_region, dim_region[region_name] = "Global")
)

UOM_Prior_Year =
VAR SelectedYear = SELECTEDVALUE(dim_time[year])
VAR PriorYear = SelectedYear - 1

RETURN CALCULATE(
SELECTEDVALUE(financial[UOM %]),
FILTER(dim_time, dim_time[year] = PriorYear &&
(dim_time[period] = "FY" || dim_time[period] = "H1")),
FILTER(dim_region, dim_region[region_name] = "Global")
)

UOM_difference =
VAR UOM_Selected_Year_Value = [UOM_Selected_Year]
VAR UOM_Prior_Year_Value = [UOM_Prior_Year]

RETURN IF(
NOT ISBLANK(UOM_Selected_Year_Value) && NOT ISBLANK(UOM_Prior_Year_Value),
DIVIDE(UOM_Selected_Year_Value - UOM_Prior_Year_Value, UOM_Prior_Year_Value) * 100,
BLANK()
)

[MichaelZang]

Hi @bhanu_gautam, I tried your code, but unfortunately, it doesn't work. It doesn't display the values