Preview Feature Dynamic Formatting (time duration format)

[crispybc]

I have seen the new preview feature for dynamic formatting and though I'd give it a go formating a duration measure (hours) into the format years,days,hours using the following formatting dax:

var col = [last status change duration]

var totaldays =  QUOTIENT(col,24)

var years = if (totaldays > 365, CONCATENATE(CONVERT(ROUNDDOWN(DIVIDE(totaldays,365),0),STRING),"y"))

var days = IF(MOD(totaldays, 365) >=1 , CONCATENATE(CONVERT(ROUNDDOWN(MOD(totaldays, 365),0),STRING),"d"))

var hours = if (MOD(col, 24) >=1, CONCATENATE(convert(TRUNC(MOD(col, 24)),STRING),"h"))

var string =  COMBINEVALUES(" ",years , days, hours)

return string

However, this is sometime returning incorrect values.

Not sure if i am doing something wrong, or it is just a bug with the new feature. Has anyone else managed to format a time duration measure into an easily readable format?

[crispybc · Accepted solution]

I figured out the issue. If your output string contains a 0, then it is substituted out for the measure value. 

You can work around this with the SUBSTITUTE function.

So new solution:

var col = [last status change duration]

var totaldays =  ROUNDDOWN(DIVIDE(col,24),0)

var years = if (totaldays > 365, CONCATENATE(CONVERT(ROUNDDOWN(DIVIDE(totaldays,365),0),STRING),"y"))

var days = IF(MOD(totaldays, 365) >=1 , CONCATENATE(CONVERT(ROUNDDOWN(MOD(totaldays, 365),0),STRING),"d"))

var hours = if (MOD(col, 24) >=1, CONCATENATE(convert(TRUNC(MOD(col, 24)),STRING),"h"))

var string =  COMBINEVALUES(" ",years , days, hours)

return SUBSTITUTE(string, "0", "\0")