sum by direct date and indirect date

[fazza1991]

Hi,

I was wondering if there is a way to get sum of two measures where the date table relationships are inactive and active.

For example:

x1 Date table

x1 Salesforce Table

There Salesforce Table contains two dates Created Date (inactive relationship) & Closed Date (active relationship)

The measures i have

Open Revenue = 
CALCULATE(SUM(ABC),
  USERELATIONSHIP(Date[Date],Salesforce[CreatedDate]))

Won Revenue = 
CALCULATE(SUM(ABC),
  (Date[Date])

However, when i sum them up and use slicer to filter specific month I get a different number

Thanks in advance

[Anonymous · Accepted solution]

Hi @fazza1991 ,

I think [Open Revenue] should be correct, you should remove (Date[Date]) in [Won Revenue] measure. 

Won Revenue = 
CALCULATE(SUM(ABC))

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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

[fazza1991 · Accepted solution]

sorry i didnt get a notification on this response but worked thank you

[Anonymous · Accepted solution]

Hi @fazza1991 ,

I think [Open Revenue] should be correct, you should remove (Date[Date]) in [Won Revenue] measure. 

Won Revenue = 
CALCULATE(SUM(ABC))

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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