Help with days hrs and mins formatting

[rosscortb]

Hello

In the attached I have tried to work out the approval times between each stage in column Step Last Routed.

In the format column it should show in days min and hrs but its putting days to 30 in some cases when it should be 0.

Also, the Approval Time is not displaying the correct date and time from the previous row.

Also, for any step last routed that has "Job Requisition Creation" in Step Description, then it should be blank in the times as this the start of the process in each job req.

Any help would be appreciated.  Attachment 

Thanks

Ross

[Fowmy · Accepted solution]

@rosscortb 

Hope this link will help you: https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[tex628 · Accepted solution]

This is abit more complacted than one might think, you need to calculate the days, hour and minutes individually. Take a look at the attached file:

Also, how is the calculation supposed to handle if it's more than 99 days? 

Br,
J

[v-shex-msft]

Hi @rosscortb,

Did these replies help with your score? If this is a case, you can consider accepting a suitable one to help others who have a similar requirement to find it more quickly.
If all of these not help, you can post here with detailed information to help us clarify your scenario.
Regards,

Xiaoxin Sheng

[mahoneypat · Accepted solution]

The "30" is coming from the date component of the result of subtracting the two dates (12/30/1899).  Duration stuff is usually easier in the query editor.  However, it can be done in DAX too (with more code).  I couldn't refresh against your source data to modify your query directly, so here is the DAX approach.  Here are two column expressions to get your desired result I believe.  You may want to change the approach on the second one to just return the cycle time in days/hours/min in decimal form so you can use it in calculations (instead of this text column).

Prev Step DateTime =
VAR thisID = Demo[Category ID]
RETURN
CALCULATE (
MIN ( Demo[Step Last Routed] ),
ALLEXCEPT (
Demo,
Demo[Category ID],
Demo[Job Req ID]
),
Demo[Category ID] = thisID - 1
)

Time Diff = var thisstep = Demo[Step Last Routed]

var laststep = Demo[Prev Step DateTime]

var diff = DATEDIFF(laststep, thisstep, MINUTE)

var days = ROUNDDOWN(diff/24/60,0)

var hours = ROUNDDOWN((diff - days*60*24)/60, 0)

var minutes = ROUNDDOWN(diff - days*60*24 - hours*60, 0)

return if(Demo[Step Description] = "Job Requisition Creation", BLANK(), days&":"&hours&":"&minutes)

[tex628 · Accepted solution]

This is abit more complacted than one might think, you need to calculate the days, hour and minutes individually. Take a look at the attached file:

Also, how is the calculation supposed to handle if it's more than 99 days? 

Br,
J

[Fowmy · Accepted solution]

@rosscortb 

Hope this link will help you: https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn