Forum Discussion
% Change over user selected year columns
Hi all, First time poster! I'd appreciate any help you can give me with this question... I need to create a dynamic matrix and line chart that shows the percentage change over selected year c...
Hi Anonymous
Maybe try something like this.
Pct Chg = VAR _CurrentSelected = [Total Value] VAR _PreviousSelected = CALCULATE( [Total Value], OFFSET( -1, ALLSELECTED( 'Table'[Year] ), ORDERBY( 'Table'[Year] ) ) ) VAR _Logic = DIVIDE( _CurrentSelected - _PreviousSelected, _PreviousSelected ) RETURN _Logic
gmsamborn
Super User
Super UserHi Anonymous
Maybe try something like this.
Pct Chg =
VAR _CurrentSelected = [Total Value]
VAR _PreviousSelected =
CALCULATE(
[Total Value],
OFFSET(
-1,
ALLSELECTED( 'Table'[Year] ),
ORDERBY( 'Table'[Year] )
)
)
VAR _Logic =
DIVIDE(
_CurrentSelected - _PreviousSelected,
_PreviousSelected
)
RETURN
_Logic
Hi there - that solution is working super well. However there's one enhancement I'd like if possible - can the earliest year also be shown as a blank column? This is necessary because having the ability to select any combination of years means that the earliest year might not be the year previous to the earliest one displayed. I hope that makes sense!
Thank you.
- gmsamborn
Hi Anonymous
I'm not really sure what you mean. Can you show me an example?