Date and Time DIFF displayed in a multiple visuals

[michael_knight]

Hi,

I'm currently working on a report where I need to display the differences between two Date and Time fields. 

I did some research on this topic and found this article to be useful to a certain extent

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

Using that article I used this piece of code

DaysHoursMinutes = var DateLastAssigned = 
DATEDIFF(MAX(Opportunity[new_leadcreatedon]),MAX(Opportunity[new_datelastassigned]),DAY) 
& " Days " & HOUR(MOD(MAX(Opportunity[new_datelastassigned]) - MAX(Opportunity[new_leadcreatedon]),1)) 
& " Hours " & MINUTE(MOD(MAX(Opportunity[new_datelastassigned]) - MAX(Opportunity[new_leadcreatedon]),1)) & " Minutes"

var dlacount = COUNTA(Opportunity[new_datelastassigned])
return

CALCULATE(IF(dlacount = BLANK(),BLANK(), DateLastAssigned),
USERELATIONSHIP('Date'[created_date], Opportunity[new_leadcreatedon]) )

These are the results from the code

When it's in a table visual it's exactly what I want, however if I want to display it on a Month to Month visual it won't work. The Measure automatically goes into Tooltips rather than Values

Does anyone have any suggestions on how to fix this issue? It doesn't have to be in relation to the article, could be sometime completely different!

Cheers,

Mike

[michael_knight]

Hi, 

Still struggling on this if anyone has any suggestions?

Cheers

[v-rzhou-msft]

Hi @michael_knight 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Rico Zhou

[michael_knight]

Hi,

Sorry, I forgot to add the sample data! Link below

https://www.dropbox.com/s/4pbc79tpo9yg59a/help.pbix?dl=0

Cheers,

Mike

[v-rzhou-msft]

Hi @michael_knight 

Due to the measure you built returns to a text type result, so you couldn't add your measure into the value of bar chart.

For more details: Cannot visualize measure values in bar chart

I build a whole number type measure to show your results by minutes.

My measure:

Measure = 
var DateLastAssigned = 
DATEDIFF(MAX(Sheet1[new_leadcreatedon]),MAX(Sheet1[new_datelastassigned]),DAY)*24*60+HOUR(MOD(MAX(Sheet1[new_datelastassigned]) - MAX(Sheet1[new_leadcreatedon]),1))*60+MINUTE(MOD(MAX(Sheet1[new_datelastassigned]) - MAX(Sheet1[new_leadcreatedon]),1))

var dlacount = COUNTA(Sheet1[new_datelastassigned])
return
IF(dlacount = BLANK(),BLANK(), DateLastAssigned)

And I build a Month/Year column in your Sheet1 by createdon

Month/Year = FORMAT(Sheet1[createdon],"mmmm")&" "&YEAR(Sheet1[createdon])

And build an active relationship between Month/Year columns in two tables.

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[michael_knight]

Hi @v-rzhou-msft ,

Thank you for the reply, seems like we're on the right track . Following on what what you posted, do you think it will be possible to convert the minutes into Days:Hours:Minutes? 

[v-rzhou-msft]

Hi @michael_knight 

I think you may want to add your DaysHoursMinutes measure into value of bar chart and when your mouse hover over "March 2018" it will show value in format" **Day**Hours**Minutes".

Currently displaying the total value as DD:HH:MM format is not possible. As Y-axis supports only numeric values currently.

So you may format your value column only like day, hours or minutes in numeric type.

You can submit a new idea to improve the Power BI: Idea
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.