Forum Discussion
Post ProdigyVisualise Date DIFF and Time DIFF
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!
Here's the PBIX File: https://www.dropbox.com/s/4pbc79tpo9yg59a/help.pbix?dl=0
I'll take any suggestions, even if it's completely different to where I'm currently at!
Cheers,
Mike
1 Reply
- lbendlin
Super User
You have not indicated what aggregation you want - do you want the total duration by month, or the average duration for all events in that month? Only events that fall completely in that month, or partial events too (starting in the month or ending in the month)? What are you eventually doing with that data?
In general when you want to do datetime/duration math, don't use datetime/duration format. Use number format with days as the whole number part and the time as the fraction. Only at the very last moment, when you display the result, convert it back to duration format.
