Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculate Year to Year Difference without Time Series Data

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.

Need Help
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    Anonymous 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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Anonymous 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
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Greg_Deckler 

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