Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Calculate Prior Year Values

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
Need Help
Show and Tell
Tutorial Requests

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous , 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()
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

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