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.

     

     

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
)