Forum Discussion

mdinius's avatar
mdinius
Frequent Visitor
3 years ago
Solved

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 f...
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    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's avatar
mdinius
Frequent Visitor
3 years ago

Sample data to help.

DateNational Common% Increase - NC
2/1/200880940.0000%
3/1/200881090.1853%
4/1/200881120.2224%
5/1/200881410.5807%
6/1/200881851.1243%
7/1/200882932.4586%
8/1/200883623.3111%
4/1/200985285.3620%
2/1/200985335.4238%
3/1/200985345.4361%
1/1/200985495.6214%
12/1/200885515.6462%
9/1/200885575.7203%
8/1/200985645.8068%
7/1/200985665.8315%
5/1/200985745.9303%
6/1/200985785.9797%
9/1/200985866.0786%
11/1/200985926.1527%
10/1/200985966.2021%
11/1/200886026.2763%
10/1/200886236.5357%
12/1/200986416.7581%
1/1/201086606.9928%
3/1/201086717.1287%
2/1/201086727.1411%
4/1/201086777.2029%
5/1/201087618.2407%
6/1/201088058.7843%
9/1/201088369.1673%
8/1/201088379.1796%
7/1/201088449.2661%
10/1/2010892110.2174%
1/1/2011893810.4275%
11/1/2010895110.5881%
12/1/2010895210.6004%
2/1/2011899811.1688%
3/1/2011901111.3294%
4/1/2011902711.5271%
5/1/2011903511.6259%
6/1/2011905311.8483%
7/1/2011908012.1819%
  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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's avatar
      mdinius
      Frequent Visitor
      3 years ago

      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
      )