How to link multiple values with multiple dates fields, using USERELATIONSHIP? Or not?

[Anonymous]

Hey guys,

This is my first time asking a question, because I've finally hit a roadblock I can't seem to cross! Hope I can explain it clearly enough. Any input would be super appreciated! Thanks, in advance!

Our company sells service agreements to customers and would like to see the renewal rates of these agreements 

(i.e. Renewed Agreements / Closed Agreements = Renewal Rate;  95 / 100 = 95% Renewal Rate).

All 'Open' agreements use a 'Start Date'.

Once an agreement closes, we classify it as 'Closed' and use an 'End Date'.

If a salesperson reups the customer, the same agreement is classified as 'Open' and 'Renewed', which both get a 'Start Date'.

I have a dim_Dates table, which I linked to the 'Start Date' field, in the fact_Agreements table (therefore, this is the active relationship).

I have written measures that count all the records that are: Open, Closed, and Renewed.

The counts for Open and Renewed are correct, when I filter (using a date slicer based on the dim_Dates table), but the counts for Closed agreements are always incorrect.

I understand that this happens because the relationship between the 'End Date' field and the dim_Dates table is inactive, but I don't know how to solve the problem.

I've attached a Visio diagram, which I've been using to try and visualize the problem. In the diagram, I've included the formula I wrote, to get around this problem.

I've tried to be as concise (and as clear) as possible, so I hope this all makes sense. Again, thanks in advance for any help or suggestions!

Regards,

Rue

[MFelix · Accepted solution]

Hi @Anonymous,

To what I can read you have an inactive relationship between End Date and your Dimension so you just need to make a USERRELATIONSHIP to use it.

Something like:

Closed = CALCULATE (COUNTDISTINC (Table[Code]); USERRELATIONSHIP (Table[End Date]; Date[Date]))

This is just a example the part that is needed for your calculation to work is the bold one that is activation of the userrelation based on end date.

On your measure in the image you have USERRELATIONSHIP on Start Date and that will not return the expect result since you keep returning to start date and not end date between visuals and slicer.

Regards,

MFelix

[Anonymous]

@MFelix Thanks so much!! I've been working on this problem for hours. I really appreciate your help!

Best regards,

Rue