cancel
Showing results for
Did you mean:
Highlighted

## Calculate difference using 2 dates in 1 table

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

## Re: Calculate difference using 2 dates in 1 table

@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]))

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User I

## Re: Calculate difference using 2 dates in 1 table

@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]))

Proud to be a Super User!

Highlighted

## Re: Calculate difference using 2 dates in 1 table

@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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors