Forum Discussion

fazza1991's avatar
fazza1991
Icon for Helper II rankHelper II
3 years ago
Solved

sum by direct date and indirect date

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
    fazza1991
    3 years ago

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    fazza1991
    Icon for Helper II rankHelper II
    3 years ago

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