cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User I
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]))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Advocate I
Advocate I

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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