I have a fact table as below:
ID OpportunityID Status CreateDate
1 A Contract Submitted 1/1/2010
2 A Action Required by Branch 1/10/2010
3 A Accepted by Customer 1/15/2010
4 B Contract Submitted 2/2/2010
5 B Action Required by Branch 2/4/2010
6 C Contract Submitted 1/5/2010
What I want to do is create a calculated filed as a measure so that each OpportunityID calculates the difference between each CreateDates for the status of an specific OpportunityID.
The result looks like as below:
OppprtunityID Status Lasting Dates
A Action Required by Branch --> Contract Submitted 10
A Accepted by Customer --> Action Required by Branch 5
B Action Required by Branch --> Contract Submitted 2
C Contract Submitted -->Today 40
I want the Lasting Dates become the measure filed in the cube.
In short, I am trying to get the amount of days in between each days represented correctly in my analysis service cube.
Can someone show me how to achieve that?