Measure to Sum duplicate Amounts by distinct ID after Merging Parent table to Child table

[Anonymous]

Hi,

I have merged a Salesforce Opportunity object (parent) to a Revenue object (child) where for each Opportunity ID, there will be multiple revenue line items due to revenue realized in installments (multiple period_start & period_end dates).

Opportunity_ID	USD_Amount	Period_Start	Period_End	Revenue	Installments
10001	19500	15-Nov-17	30-Nov-17	208454	1
10001	19500	1-Dec-17	31-Dec-17	391094.63	2
10001	19500	1-Jan-18	19-Jan-18	50451	3
10001	19500	1-Feb-18	28-Feb-18		4
10001	19500	1-Mar-18	31-Mar-18		5
10001	19500	1-Apr-18	30-Apr-18		6
10002	1500	1-Sep-17	1-Sep-17	426	1
10002	1500	1-Aug-17	31-Aug-17	12504.26	2
10002	1500	28-Jul-17	31-Jul-17	2070	3
10003	50000	1-Jan-19	31-Jan-19	39370	1
10003	50000	1-Feb-19	28-Feb-19	39370	2
10003	50000	1-Mar-19	1-Mar-19	1260	3
10004	230000	1-Jan-18	26-Jan-18	41657	1
10004	230000	22-Nov-17	30-Nov-17	9903	2
10004	230000	1-Dec-17	31-Dec-17	78439.8	3

I want to create 2 measures:

- Opportunity distinct numbers [ Opp(#) ]

- Opportunity distinct USD_Amount [ Opp($) ]

As you can see, USD_Amount is duplicated for each Opportunity ID due to which the Opportunity $ shows up incorrect results (multiplied). I have the following 2 measures that worked on the parent Opportunity before the Merge, but not working now after the Merge with the child Revenue object. I want to modify these 2 measures to calculate the distinct USD_Amounts based on distinct Opportunity ID's and LastDate of Period_End or LastDate of Period_Start.

Opps(#) = CALCULATE(DISTINCTCOUNT('FACT'[Opportunity_ID_18__c]),'FACT'[USD_Amount__c] <= 1000000,USERELATIONSHIP(dimDate[Date],'FACT'[CreatedDate]))

Opp($) = 
CALCULATE( SUM('FACT'[USD_Amount__c]), 'FACT'[USD_Amount__c] <= 1000000, USERELATIONSHIP(dimDate[Date],'FACT'[CreatedDate]))

How do i incorporate LastDate of Period_End or LastDate of Period_Start in the above measures to sum up the correct USD_Amounts per Opportunity ID?

Any help would be most appreciated.

[v-xiaotang]

Hi @Anonymous 

How do i incorporate LastDate of Period_End or LastDate of Period_Start in the above measures to sum up the correct USD_Amounts per Opportunity ID?

not very sure, could you add expected result on your data table? and how to calculate it. then I'll write the measure later.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please explain what the rules are for calculating the revenue amount for a timeframe.