Compare Historical/Changed data

[Richard_Halsall]

Hi, I am looking for advice

I have 3 tables:

DimOpportunity - containing sales opportunities

FactRevenue - containing current expected revenues against the opportunity on a monthly basis

OpportunityFieldHistory - containing dates, stage status and probability percentage

An opportunity goes through 5 stages over its date range with a different probability percentage at each stage. Total opportunity revenue is calculated by the sum of revenues multiplied by the stage percentage, the model looks as below:

I am trying to calculate things all based on relative date selection, opportunity total revenue (calculated as sum of revenue * stage probability) and stage these include:

New opportunities in the last 7 days and their total revenue value i.e. changetype = 'created'- completed

Opportunitiesin the last 7 days cancelled and their total revenue value  i.e. changetype = 'cancelled'- completed

Opportunities and their total revenue value where there has been no change in their stage in the last 7 days i.e. changetype today equals changetype 7 days ago so value is exactly the same now as it was 7 days ago- stuck

Opportunities and their total revenue value where there has been a change in their stage in the last 7 days i.e. changetype today does not equal changetype 7 days ago so value will be different now as it was 7 days ago- stuck

As a further complication an opportunity may 'pass' through multiple stages in a time period so need to only look at the latest stage.

7 days could be 14 days could be 30 days etc

Any help much appreciated, thanks

[v-alq-msft]

Hi, @Richard_Halsall 

Could you please show us some sample data and expected results with OneDrive for business? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Richard_Halsall]

Hi Allan

I have attached a data file (excel) with sample data from the 3 main tables plus a results tab. If further clarification is required please let me know.

Many thanks

Richard

Data File