Calculate difference using 2 dates in 1 table

[btalahmbong]

Hi,

I have 2 dates column in my case table; 'created on' & 'resolved on'. I want to calculate the net difference between cases created and reolved on.

So far, I have 2 measures to calculate the sum of cases created and resolved. These are based on each table. Then, I have a measure calculating the difference of these 2 measures.

The problem occurs when putting the net difference measure on a visual. If I use the 'created on' date, the cases resolved will be calculated based on the date they were created as opposed to when the were resolved and vice versa.

How can I have a date column that will adhere to both date conditions ('created on' & 'resolved on') when put on a visual to show the net difference?

Thanks!

[vanessafvg · Accepted solution]

@btalahmbong you need to use the userelationship function

when you create relationships to your calendar table (assuming you have one?), there can be only one primary (active) relationship, that will be the default, the inactive relationship will have a dotted line 

can you post a pic of your relationships.

therefore for your measure for the inactive relationship, say its the resolved on date

calculate(measure, userelationship([table]resolvedondate, calendar[date]))

[btalahmbong]

@vanessafvg Thanks a lot. 

I already had an inactive relationship (Case[modified.Id], User[User Id]) on the 'cases resolved' measure so didn't know it was possible to have more than 1 inactive relationship on a measure. But that worked perfectly. Thanks a lot.