Converting Minutes to DDHHMM format, negative not showing correctly

[min-E]

Hello,

I have not been able to find an answer to these questions.

I have duration in minutes from the system. PBI Datediff gives the same results. No issue.

_ Datediff PartOrder to Delivered Minute = 
DATEDIFF([Part Order],Part Delivered],MINUTE)

I am using this to display my Duration-In-Minutes as a 00D 00H 00M format.
It works for positive minutes, but for negative it is showing incorrect.

_PartOrder to PartDelivered DHM = 
var Days=INT([PartOrder to PartDelivered Minutes]/1440)
var Hours=INT(MOD([PartOrder to PartDelivered Minutes] / 1440,1 ) * 24)
var Minutes= INT(MOD(MOD([PartOrder to PartDelivered Minutes]/1440,1)*24,1)*60)
RETURN
FORMAT(Days,"#00") & "D " & FORMAT(Hours,"#00") & "H " & FORMAT(Minutes,"#00") & "M"

So I went and added a DateDiff for each, Days Hours Minutes. And added this column as Formatted. It does show correctly.

Format = 
FORMAT([Order to Deliver Days],"#00") & "D " & FORMAT([Order to Deliver Hours],"#00") & "H " & FORMAT([Order to Deliver Minutes],"#00") & "M"

Questions:

1) Is there a way to consolidate the measures? Or is it correct?

2) Since Formatted is, well, formatted, you can't use it to sort - as you could with the Minutes. Is there a way to fix this?

Any help is appreciated. Thank you!!

[Anonymous · Accepted solution]

Hi @min-E ,

The error in your original code is due to the presence of a negative number, which can cause an error when performing math calculations. You can add a judgment condition to your original code. For example:

_PartOrder to PartDelivered DHM = 
VAR Days =
IF(
    [PartOrder to PartDelivered Minutes] >= 0,
    TRUNC([PartOrder to PartDelivered Minutes] / 1440),
    TRUNC((-[PartOrder to PartDelivered Minutes]) / 1440)
)
VAR Hours = 
IF(
    [PartOrder to PartDelivered Minutes] >= 0,
    TRUNC(MOD([PartOrder to PartDelivered Minutes], 1440) / 60),
    TRUNC(MOD((-[PartOrder to PartDelivered Minutes]), 1440) / 60)
)
VAR Minutes = 
IF(
    [PartOrder to PartDelivered Minutes] >= 0,
    MOD([PartOrder to PartDelivered Minutes], 60),
    MOD((-[PartOrder to PartDelivered Minutes]), 60)
)
RETURN
IF(
    [PartOrder to PartDelivered Minutes] >= 0,
    CONCATENATE(
        Days & "D " & Hours & "H " & Minutes & "M",
        ""
    ),
    CONCATENATE(
        (0-Days) & "D " & (0-Hours) & "H " & (0-Minutes) & "M",
        ""
    )
)

If it is a negative number, it is first converted to a positive number for calculation.
And the final output is as below:

Regarding the sorting question, since your column is in your custom format, it is a text type column, and text types can't sort data from largest to smallest like the [MINUTE] column. Although you can do this by customizing the sorting, it requires you to define a sort order for each row in the table, which is quite cumbersome and I don't recommend it.

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

[min-E]

Dino, thank you for taking the time to explain. This absolutely makes sense. 

As far as sorting, thanks for the confirmation. The minutes can be used if it's that big of a deal.