Cumulative % increase, based on a parameter

[mdinius]

Hey all! I'm looking to create a curve showing the %increase from a selected date to a fixed date.

I've created a parameter for the date input, which filters the column of values. The part I can't figure out is how to pass the "National Common" value from the first month to calculate the percent month over month.

Here is the excel version:

I'm starting to think that I'm missing some sort of dynamic index to store the first value. Any help would be appreciated. 

[mdinius]

Sample data to help.

Date	National Common	% Increase - NC
2/1/2008	8094	0.0000%
3/1/2008	8109	0.1853%
4/1/2008	8112	0.2224%
5/1/2008	8141	0.5807%
6/1/2008	8185	1.1243%
7/1/2008	8293	2.4586%
8/1/2008	8362	3.3111%
4/1/2009	8528	5.3620%
2/1/2009	8533	5.4238%
3/1/2009	8534	5.4361%
1/1/2009	8549	5.6214%
12/1/2008	8551	5.6462%
9/1/2008	8557	5.7203%
8/1/2009	8564	5.8068%
7/1/2009	8566	5.8315%
5/1/2009	8574	5.9303%
6/1/2009	8578	5.9797%
9/1/2009	8586	6.0786%
11/1/2009	8592	6.1527%
10/1/2009	8596	6.2021%
11/1/2008	8602	6.2763%
10/1/2008	8623	6.5357%
12/1/2009	8641	6.7581%
1/1/2010	8660	6.9928%
3/1/2010	8671	7.1287%
2/1/2010	8672	7.1411%
4/1/2010	8677	7.2029%
5/1/2010	8761	8.2407%
6/1/2010	8805	8.7843%
9/1/2010	8836	9.1673%
8/1/2010	8837	9.1796%
7/1/2010	8844	9.2661%
10/1/2010	8921	10.2174%
1/1/2011	8938	10.4275%
11/1/2010	8951	10.5881%
12/1/2010	8952	10.6004%
2/1/2011	8998	11.1688%
3/1/2011	9011	11.3294%
4/1/2011	9027	11.5271%
5/1/2011	9035	11.6259%
6/1/2011	9053	11.8483%
7/1/2011	9080	12.1819%

[bolfri · Accepted solution]

National Common Sum = SUM('Sample'[National Common])

First date = CALCULATE(FIRSTDATE('Sample'[Date]),ALLSELECTED('Sample'))

First day value =
var first_date = [First date]
return CALCULATE([National Common Sum],FILTER(ALLSELECTED('Sample'),'Sample'[Date]=first_date))

 % Increase - NC = DIVIDE([National Common Sum]-[First day value],[First day value])

You can use a simple date slicer.

[mdinius]

Thank you! That worked well. I have a few questions:

National Common Sum - Why was this required instead of just using [National Common]? Could this have been achieved by changing the "Summarization" to Sum for [National Common]?

I ended up combining this into one measure. Would you mind providing some notes on the Calculate, Filter, and AllSelected functions? 

%Increase - NC =

VAR _first_date =

CALCULATE(

    FIRSTDATE('Inflation Rate Table'[Date]),ALLSELECTED('Inflation Rate Table')

)

VAR _first_date_value =

CALCULATE(

    'Inflation Rate Table'[National Common Sum],

    FILTER(ALLSELECTED('Inflation Rate Table'),'Inflation Rate Table'[Date]=_first_date)

    )

RETURN

DIVIDE(

    'Inflation Rate Table'[National Common SUM]-_first_date_value,_first_date_value

)