Calculating % change between 2 dates

Hi all, 

I want to show a pivot line chart of 3 things:

1) Index values

2) Index % change (from date to date)

3) Accumulative change

I have 2 relevant tables:

1) Date table - DateTBL

2) Indexes table- UnitedIndexesDB

To accomplish bullet #(2), I tried to type a DAX formula, but I am missing something:

 Index % change:= (CALCULATE(SUM(UnitedIndexesDB[Value]))/CALCULATE(SUM(UnitedIndexesDB[Value]),FILTER(UnitedIndexesDB,UnitedIndexesDB[Date]<MAX(UnitedIndexesDB[Date]))))-1

Attached is the output (orange line):

When I look at the PP measure I see an answer, but in the chart the value=0, not sure why... 

I think that it might be related to that the index doesn't have a value for each day, while the dates data table that is used as the X axis, does have a date for every day. Also, when there is no data it shows 0 instead of ignoring the date. 

 

If you also can help in answering how bullet#3 can be accomplished that will really help.

Thank you

July 16th, 2015 10:14am

Are you sure that the value is really 0 in the chart and not just really close to zero. Your scale in your chart has the number 6000 as it's top value but percentages are really small, like .56 or .20 or even .03 or .006.  Not sure that you can put both of these on the same chart and get the effect that you desire. What you would have to do is to either multiply your % change by a large number in order to make it have the same basic scale as your other value or take your other value and bring it down to the same scale/order of magnitude of your % change.

Your chart is trying to display values that are at least 4 orders of magnitude apart, that's just not realistic. 


Free Windows Admin Tool Kit Click here and download it now
July 17th, 2015 1:33pm

Hi, 

You are right, though in my Excel I created a secondary Y axis :)

Still- the value is 0. 

So this is a good catch, but isn't the solution unfortunately....

Appreciate your help!

 

July 17th, 2015 5:44pm

Hi, 

Please assist...

Free Windows Admin Tool Kit Click here and download it now
July 19th, 2015 1:46am

Your date filter expressions in your formula should reference your date table and not your Index table.

If one imagines the CALCULATE as PPs SUMIF-Function Im wondering where you need the first CALCULATE for, as there are no conditions following.

If youre dividing sth in PP, Id strongly recommend using the DIVIDE function.

Although difference from date to date sounds trivial, in PP world, where you working with calendar mechanisms, you would need to define how to deal with aggregations/bigger scales: Which value to take if multiple index values land in one of your time units (i.e. multiple index values in one month which sits on your x-axis): Should it then be months averages compared to their respective previous one or the latest values in each month for example? If it is averages: Weighted by duration or not?

You should then start your task by building every single value as a separate measure in order to be able to track & check what is happening in a table:

  1. MeasureWithTheValueToCompare:=
  2. MeasureWithTheValueToCompareAgainst:=
  3. Difference:= 1)-2)
  4. Difff%:=DIVIDE(3), 2))-1

Apply different x-axis selections against them & check if they still work.

Last step would be to create the chart J

July 19th, 2015 3:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics