Calculate Year to Year Difference without Time Series Data

[MichaelZang]

Hi everyone,

Apologies for reposting a similar issue. I've gone through several previous posts and solutions shared by others regarding this common problem, but unfortunately, I haven't been able to apply any of them successfully to my specific context.

Context: I'm trying to calculate the difference between the selected year (via slicer) and the prior year.

Problem: The matrix correctly displays data for the selected year but shows nothing for the prior year.

Attempts so far:
I've tried multiple approaches, including adding a prior_year column in the dim_time table and experimenting with various DAX formulas. Below is my latest DAX attempt, which still doesn’t work as expected.

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 %]),
REMOVEFILTERS(dim_time[year]),
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()
)

Thank you for looking through my issue. I'd appreciate it if you could drop any explanation/hint/solution on this issue.

[Greg_Deckler · Accepted solution]

@MichaelZang Try this:

UOM_Prior_Year 1 = 
    VAR SelectedYear = SELECTEDVALUE(dim_time[year])
    VAR PriorYear = SelectedYear - 1
    VAR __Company = MAX( 'dim_company'[company_id] )
    VAR __Dates = SELECTCOLUMNS( FILTER( ALL( 'dim_time' ), [period] = "FY" || dim_time[period] = "H1"), "Date", [date_key] )
    VAR __Regions = SELECTCOLUMNS( FILTER( ALL( 'dim_region' ), dim_region[region_name] = "Global" ), "Region", [region_id] )
    VAR __Table = FILTER( ALL( 'financial' ), [date_key] IN __Dates && [region_id] IN __Regions && [company_id] = __Company )
    VAR __Result = SUMX( __Table, [UOM %] ) 
RETURN
    __Result

[MichaelZang]

Hi @Greg_Deckler 

Thank you for your prompt solution. I appreciate it a lot 😁

[MichaelZang]

PBIX LINK: HERE