Problems with Calendar to get a timeline column chart

[FrankMcQ]

Hi

I have three sources of information describing a job start & end dates, when jobs were quoted and when jobs were invoiced. They are all linked by a Job ID.

I'm trying to create a timeline chart that would allow me to see the amount quoted and invoiced for each month. Something like:

Invoice & Quote Timeline

I built a model and I'm getting into trouble with relationships to the dates not calculating as I expected. I either get the quotes correct or the invoices correct based on what link I make active.

Here is the model:

and the resulting Chart:

I can make the quotes relationship active then I get the right results for quotes, likewise for invoices I get them right if I set it as active, but the quotes needs to be inactive.

Is there a way I can get both relations working so the chart gives me the correct values in the correct month?

Any help would be gratefully received.

Here is a link to the test model I built... Link 

[v-easonf-msft · Accepted solution]

Hi . @FrankMcQ 

If  Table "Quotes" and Table "Calendar " relationship is inactive.

Take a try to create a measure  by using  function of "userelationship  "  and put the measure into the visual to replace the field of "quote value" .

Measure = CALCULATE(SUM(Quotes[Quote Value]),USERELATIONSHIP(Quotes[Quote Date],'Calendar'[Date]))

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.