% of base year

[Anonymous]

Hi, I would like to create a measure % of Base year, based on the launch year I select in a slicer (see below an example with launch year 2012).

I tried to calculate a measure "revenue launch year", as a mid-step, but I failed to generate/populate the numbers colored red (239k). Do you know how to generate such formula or to calculate the "% of base" in some other way?

Year	LaunchYear	Revenue	Revenue Launch Year	% of Base
2017	2012	6,167	239,032	2.6%
2016	2012	37,505	239,032	15.7%
2015	2012	57,548	239,032	24.1%
2014	2012	80,872	239,032	33.8%
2013	2012	104,734	239,032	43.8%
2012	2012	239,032	239,032	100.0%

Thank you

[MFelix · Accepted solution]

Hi @Anonymous,

Create a table with the years that you want to use as base I have made a calculated table based on DAX with the following formula:

LaunchYear = DISTINCT(Revenue[Year])

Then use this table as your slicer and use the following measures to calculate your values:

RevenueLaunchYear = var Launch_Year = MAX(LaunchYear[Year])
Return
CALCULATE(SUM(Revenue[Revenue]); Revenue[Year] = Launch_Year)

% of Base = SUM(Revenue[Revenue])/[RevenueLaunch Year]

The RevenueLaunchYear measure is not necessary to have you can only make this single formula.

% of Base =
VAR Launch_Year =
    MAX ( LaunchYear[Year] )
VAR RevenueLaunchYear =
    CALCULATE ( SUM ( Revenue[Revenue] ); Revenue[Year] = Launch_Year )
RETURN
    SUM ( Revenue[Revenue] ) / RevenueLaunchYear

Another note to this is that by using MAX if you don't select any value on the slicer it will give you the greater value (2017) when you select some value the calculations will be made accordingly.

See below the result and attached PBIX file.

Regards,

MFelix