Hello
Is there a way to get the Datediff family of functions to return the results in terms of years, months and days instead of just days?
Most of our issues are in year and month range, and it would be good to display time in status or age in terms of these more meaningful measure than just days. For many if they see days in status of 1329 they can’t relate and it may as well be 114825600 seconds because it’s equally intangible.
There seems to be the function Datediff that shows up in autocomplete which looks promising but it’s not in any of the documentation I can find. No matter what I plug into the String section, I get an error.
For now I have created the following abomination of a measure that attempts to turn raw days into years, months and days. It’s not perfect but it’s more useful to those viewing the data than raw days.
If I want to show oldest, I first sort by the standard measure in days.
Issue Days in Status
DateDiffDays([Measures].[Issue status updated date], Now())
Then hide it and just display the measure below.
This is okay until somebody interacts with the data and sorts by another field, then there’s no way to sort again by this measure because it’s plain text and the dashboard has to be refreshed to restore the order.
Time in status
CASE WHEN
[Measures].[Issue Days in Status]>0
THEN
--note this rounds the day up
iif(IsEmpty(nonzero(fix(([Measures].[Issue Days in Status]/365.25)))),"",cast(format(nonzero(fix(([Measures].[Issue Days in Status]/365.25))),"#") as string)
||"y ")
||
iif(IsEmpty(nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12))),"",cast(format(nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12)),"#") as string)
||"m ")
||
iif(IsEmpty(([Measures].[Issue Days in Status]-365.25*nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12))/12-365.25*nonzero(fix(([Measures].[Issue Days in Status]/365.25)))) - MOD(([Measures].[Issue Days in Status]-365.25*nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12))/12-365.25*nonzero(fix(([Measures].[Issue Days in Status]/365.25)))), 1) + 1),
"",cast(format(([Measures].[Issue Days in Status]-365.25*nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12))/12-365.25*nonzero(fix(([Measures].[Issue Days in Status]/365.25)))) - MOD(([Measures].[Issue Days in Status]-365.25*nonzero(Fix((
([Measures].[Issue Days in Status]/365.25) - fix([Measures].[Issue Days in Status]/365.25)
)
*12))/12-365.25*nonzero(fix(([Measures].[Issue Days in Status]/365.25)))), 1) + 1,"#") as string)||"d")
END
It works on the assumption that there’s 365.25 days in a year, and 12 months in a year, so 365.25/12=30.4375 average days in a month. Works out years, then months then whatever’s left over is the days. Again, not perfect by any stretch but more relatable and usable.