Forum Discussion
Cumulative % increase, based on a parameter
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.
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.
3 Replies
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% 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.
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))RETURNDIVIDE('Inflation Rate Table'[National Common SUM]-_first_date_value,_first_date_value)
