Current Semester - Previous Semester in a chart

[#1]

We have a very simple fact table, as shown below,  that updates every six months and where AWR may change every semester. I want to create a chart where I want to display ID, Name, Current AWR and Change AWR (Current AWR- Previous AWR). Can someone help design an expression for it. We only carry two current semesters, so in july-2012 records from June-2011 will be dropped. Expression is still excpected to work as (current - previous). Also there is a (Semester) as a filter on top of the chart. Irrespective of the semester selected, the expression should always give (current - previous AWR). Thanks.

ID	Name	AWR	Semester
111	Abc	10	June-2011
111	Abc	6	December-2011
222	Xyz	5	June-2011
222	Xyz	2	December-2011

[swuehl · Accepted solution]

Would be good if you could assign a date to your semester value, based on year and month, with a numerical representation.

Load

...

if(left(Semester,1)='J',makedate(right(Semester,4),6), makedate(right(Semester,4),12)) as SemesterDate,

...

from Table;

Since you only have the latest two Semester in your data, I think you can then calculate the change like

=sum({<Semester= , SemesterDate = {'$(=max({1}SemesterDate))'} >} AWR) - sum({<Semester= ,SemesterDate = {'$(=min({1}SemesterDate))'} >} AWR)

edit: attached a sample

[#3]

Thanks So much. It worked!