percent of change from one year to the next

[Anonymous]

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

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Do you like something like this?

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.

[Anonymous]

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

[Anonymous]

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