Creating index chart showing trend from the baseline year

[InhabitedIsland]

Hi everyone,

I have a simple problem to solve but no easy way to solve it. I have a dataset where values are given for a period of time for each year-month period. I also have various dimensions of these values [Dimension 1], [Dimension 2], etc. What I need is to be able to create an index chart which shows based on these parameters the % change of the value over the historical period based on the time period selection I make. I should be able to use the slicers to choose the start and end year, specific months for which to sum up the value and all the dimensions.

The result should look like this:

Year	New Cases	IndexNewCases
2015	10107	100%
2016	12543	124%
2017	10164	101%
2018	9975	99%
2019	3128	31%

I have the following formula which almost works:

IndexNewCases = 

divide(
sum('Data'[New Cases]);
calculate(sum('Data'[New Cases]);
filter
(
ALLEXCEPT('Data';'DIM_Region'[Region];'DIM_BU'[BU];'DIM_LOB'[Line of Business];'DIM_Type'[Type];'Data'[Month]);
'Data'[Year]=2015
)
)
)

Now, I would like to be able to change this '2015' above to something that will be selected dynamically based on the minimum Year I choose with my slicer. Something like:

IndexNewCases = 

VAR MinYear = MIN('Data'[Year])

RETURN

divide(
sum('Data'[New Cases]);
calculate(sum('Data'[New Cases]);
filter
(
ALLEXCEPT('Data';'DIM_Region'[Region];'DIM_BU'[BU];'DIM_LOB'[Line of Business];'DIM_Type'[Type];'Data'[Month]);
'Data'[Year]=MinYear
)
)
)

Unfortunately the method above doesn't work as I get 100% in each year from 2015:

Year	New Cases	IndexNewCases
2015	10107	100%
2016	12543	100%
2017	10164	100%
2018	9975	100%
2019	3128	100%

Also it should work correctly when I choose specific months because right now it doesn't work right:

Year	Month	New Cases	IndexNewCases
2015	January	1053	10%
2016	January	1217	12%
2017	January	1016	10%
2018	January	792	8%
2019	January	726	7%

I have tried to add MIN formula to the filter settings but it doesn't work. My attempt above to solve it through a variable also failed. 

Any help will be very much appreciated!

Cheers!

[Anonymous]

-- Let's say you've got a Calendar (as you should)
-- in your model that covers all the full years
-- for which there is at least one date in any of
-- your tables and the Calendar is marked as a
-- DATE TABLE. You should make a relationship
-- from 'Calendar'[Date] to the date field in
-- your fact table, 'Data'.

-- You should also have a disconnected table, 'Minimum Years',
-- with a column that will be used for a slicer.
-- Say the col name is 'Minimum Years'[Minimum Year] and its data type
-- should be the same as the 'Calendar'[Year]'s column.

-- First of all, you should create basic measures.
-- Here's one you should have defined upfront:

[# New Cases] := SUM( 'Data'[New Cases] )

-- Second, here's probably the calc you're after:

var __currentCases = [# New Cases]
var __minimumYear = SELECTEDVALUE( 'Minimum Years'[Minimum Year] )
var __currentCasesForMinimumYear =
	CALCULATE(
		[# New Cases],
		'Calendar'[Year] = __minimumYear,
		ALL( 'Calendar' )
	)
RETURN
	DIVIDE( __currentCases, __currentCasesForMinimumYear )

Best

Darek

[InhabitedIsland]

Hi Darek,

First of all, thanks a million! As you suggested I did create a separate calendar for Minimum Years and it works beautifully:

But I do have one more question: the solution does not work when I also try to specify months within a year - to be able to compare the same periods across several years. It starts taking % of the total year result for the minimum year:

What shall I do in this case? Shall I add months to my Minimum Year table? Or is there another way?

Thanks!

[Anonymous · Accepted solution]

var __currentCases = [# New Cases]
var __minimumYear = SELECTEDVALUE( 'Minimum Years'[Minimum Year] )
var __currentCasesForMinimumYear =
	CALCULATE(
		[# New Cases],
		'Calendar'[Year] = __minimumYear
	)
RETURN
	DIVIDE( __currentCases, __currentCasesForMinimumYear )

The logic above keeps all filters on the 'Calendar' table apart from the year. The year is shifted to the selected value. Please note this will work with month, quarter and other date entities that are not specifically tied to a given year. So, for instance, if you slice by month "2018-JUN", the measure will return BLANK because there is no "2018-JUN" in the year 2019, for instance. But if you slice by "JUN" itself, you'll get what you need because the shifted year does have data for "JUN" and the period selected/filtered will be June 2019.

With DAX it's all about knowing how your data is structured and how CALCULATE applies filters.

I would also suggest that you give the tables and entries in your tables PROPER NAMES that will make it easy for the user to distinguish among them and know what they are for just by a quick look. For instance, I'd rename the 'Minimum Year' parameter table to 'Base Year'. Naming is of utmost importance when building understandable and complex models.

Best

Darek