cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stwg Frequent Visitor
Frequent Visitor

percent of change from one year to the next

Hi All;

Here's my delemia:

I have a list of companies, and data that is PER year for 5 years.  In matrix form, I have the years accross the top and the company's listed down the side.  In each year column, is a a figure - this is a stand alone number that is not to be summed, totaled or in a running balance.

example:

                20000           2001              2002          2003     2004

Xyz           10                  2                  -3                21         5

ABC          -6                 0                   5                 2           -1

Easy         18                 21                35              40           40

 

What I need is to show the % of change from one year to the next.

 

I think I need to do a measure where I say something like:

'00-01%chg = CALCULATE [field number], SAMEPERIODLASTYEAR [Year]

and than do another measure

Var=[field number]-SAMEPERIODLASTYEAR [Year],DIVIDED [field number]

 

Of course the above doesn't work.....in excel I'd just create a column between each year, put the formula of =Sum(a12-c12)/a12

 

Thanks for your help

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: percent of change from one year to the next

Hi @stwg 

Do you like something like this?

7.png

Create measures

Measure = CALCULATE(SUM(Sheet7[value]),FILTER(ALLEXCEPT(Sheet7,Sheet7[company]),Sheet7[year]=MAX(Sheet7[year])-1))

Measure 2 = (SUM(Sheet7[value])-[Measure])/SUM(Sheet7[value])

If so, please let me know how you would like to handle the "infinity" value.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support Team
Community Support Team

Re: percent of change from one year to the next

Hi @stwg 

Do you like something like this?

7.png

Create measures

Measure = CALCULATE(SUM(Sheet7[value]),FILTER(ALLEXCEPT(Sheet7,Sheet7[company]),Sheet7[year]=MAX(Sheet7[year])-1))

Measure 2 = (SUM(Sheet7[value])-[Measure])/SUM(Sheet7[value])

If so, please let me know how you would like to handle the "infinity" value.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

stwg Frequent Visitor
Frequent Visitor

Re: percent of change from one year to the next

Hi Maggie,

Thanks for your reply - sorry for not responding sooner, its been a crazy week

 

The short answer is yes, you have created what I'm looking to accomplish - only 'Measure' keeps returning an error in the formulation - I've tried various things to fix it, but I'm not sure what the =MAX('Sheet7'[year])-1) does. 

 

right now I have -

YtoYmacthup = CALCULATE
(SUM('Financial Data'[ROE]),
FILTER(ALLEXCEPT('Financial Data','Financial Data'[Company Name],'Financial Data'[Year]),
=MAX('Financial Data'[Year],-1))))
 
Which is returning the error:
The syntax for '=' is incorrect. (DAX(CALCULATE(SUM('Financial Data'[ROE]),FILTER(ALLEXCEPT('Financial Data','Financial Data'[Company Name],'Financial Data'[Year]),=MAX('Financial Data'[Year],-1)))))).
OR if I take out the = sign I get:
A single value for column 'Year' in table 'Financial Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
Thanks so much for all your help
stwg Frequent Visitor
Frequent Visitor

Re: percent of change from one year to the next

Hi Maggie;

As I've been searching for how to correct the formula, I wonder if my Year field is the issue?  Right now its formated as "Whole Number".  When I change it to Date, all my years become 1905 (regardless of what Year it actaully is). 

 

Also note that when my Year column in visually used as a column (vs as a label accross the top) it displays as 6048 (regardless of what Year it actaully is).

 

Thank you