- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculate Elapsed Time for Different Statuses

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

AndrewDang

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2016
02:21 PM

Hello all;

I am trying to calculate elapsed time for different statuses for a work order. For example, I have a work order with different status below. I would like to calculate elapsed time between two statuses, CEST and CPO, for example. It may return 0 or the difference. Please let me know if you have any suggestion on this.

Thanks in advance for your help.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

AndrewDang

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-23-2016
11:07 AM

I have found the solution to this problem with help from a friend. I have created the following:

StatusStartDate =

CALCULATE(

FIRSTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup],v_WorkOrderStatuses[StatusCode])

)

StatusEndDate =

CALCULATE(

LASTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup], v_WorkOrderStatuses[StatusCode]) )

and the durantion calculation below which returns an integer so that I could use it for my chart:

StatusDuration =

VAR StartDate = CALCULATE(

FIRSTNONBLANK(v_WorkOrderStatuses[StatusStartDate], TRUE),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])

)

VAR EndDate = CALCULATE(

LASTNONBLANK(v_WorkOrderStatuses[StatusEndDate], TRUE),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])

)

RETURN

IF( StartDate = EndDate,

(DATEDIFF(StartDate, TODAY(), DAY)),

DATEDIFF(StartDate, EndDate, DAY)

)

Let me know if you have any question on this.

6 REPLIES 6

ankitpatira

Super User

Re: Calculate Elapsed Time for Different Statuses

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2016
09:34 PM

@AndrewDang You need to go to query editor and add Index column (starting 0) under Add Column tab to your dataset first. Once done then simply create calculated column using below DAX which will give you difference in seconds,

Difference = ROUND(24. * 60 * 60 * (

YOURTABLE[timeColumn] - IF(

YOURTABLE[IndexColumn] = 0,

YOURTABLE[timeColumn],

(LOOKUPVALUE(

YOURTABLE[timeColumn],

YOURTABLE[IndexColumn],

YOURTABLE[IndexColumn]-1)

))),1)

AndrewDang

Member

Re: Calculate Elapsed Time for Different Statuses

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2016
11:07 AM

Thanks @ankitpatira

I have created the index colume as suggested by you. However, the index does not necessary start from 0 since we have too many other work order on the list.

The image below shows the index for this particular work order.

I have also created the following Measure but it does not seem to yield the right result. I am playing with this now but would love to have a hint or two from you if possible.

Difference = ROUND(24. * 60 * 60 * (MIN(factWorkOrderStatuses[StatusDateTime]) - IF(

MIN(factWorkOrderStatuses[Index]) = 0,

MIN(factWorkOrderStatuses[StatusDateTime]),

(LOOKUPVALUE(

factWorkOrderStatuses[StatusDateTime],

factWorkOrderStatuses[Index],

MIN(factWorkOrderStatuses[Index])-1)

))),1)

v-caliao-msft

Moderator

Re: Calculate Elapsed Time for Different Statuses

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2016
07:02 PM

Hi @AndrewDang,

If you would like to calculate the time duration between CEST AND CPO based on the same workorder, then we may take a try with the formula using calculated column below:

elapsedtime1 = DATEDIFF(

LOOKUPVALUE(Sheet1[StatusDateTime], Sheet1[workOrderNumber], value(Sheet1[workOrderNumber]), Sheet1[StatusCode],"CEST"),

LOOKUPVALUE(Sheet1[StatusDateTime],Sheet1[workOrderNumber],value(Sheet1[workOrderNumber]),Sheet1[StatusCode],"CPO"),

SECOND)

DATEDIFF Function would calculate the duration between two dates, count the result in seconds, minutes or hours, based on the last parameter configured, see:

DATEDIFF Function

And here we use LOOKUPVALUE function to locate the needed two dates.

LOOKUPVALUE Function (DAX)

See the testing result:

If any further assistance needed, please feel free to post back.

Regards,

Charlie Liao

Highlighted
##

AndrewDang

Member

Re: Calculate Elapsed Time for Different Statuses

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2016
01:55 PM

Thanks @v-caliao-msft. I really appreciate your help here.

I have created the calculated column as suggested by you but I run into this error. I will continue to troubleshoot this but any hint from you is also appreciated. The error is: "A tabble of multiple values was supplied where a single value was expected". It looks like it is confused with the value that we tried to pass to the compare.

Thanks;

Andrew

AndrewDang

Member

Re: Calculate Elapsed Time for Different Statuses

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-16-2016
10:27 AM

Charlie;

I don't know why but I have not been able to get the formula to work yet. I keep getting the same error "A table of multiple values was supplied where a single value was expected" error. I have been googling but have not been able to identify the root cause of this.

Let me know if you can take another look?

Thanks;

Andrew

AndrewDang

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-23-2016
11:07 AM

I have found the solution to this problem with help from a friend. I have created the following:

StatusStartDate =

CALCULATE(

FIRSTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup],v_WorkOrderStatuses[StatusCode])

)

StatusEndDate =

CALCULATE(

LASTNONBLANK(v_WorkOrderStatuses[StatusDateTime], TRUE()),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[ChangeGroup], v_WorkOrderStatuses[StatusCode]) )

and the durantion calculation below which returns an integer so that I could use it for my chart:

StatusDuration =

VAR StartDate = CALCULATE(

FIRSTNONBLANK(v_WorkOrderStatuses[StatusStartDate], TRUE),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])

)

VAR EndDate = CALCULATE(

LASTNONBLANK(v_WorkOrderStatuses[StatusEndDate], TRUE),

ALLEXCEPT(v_WorkOrderStatuses, v_WorkOrderStatuses[WorkOrderNumber], v_WorkOrderStatuses[StatusCode], v_WorkOrderStatuses[ChangeGroup])

)

RETURN

IF( StartDate = EndDate,

(DATEDIFF(StartDate, TODAY(), DAY)),

DATEDIFF(StartDate, EndDate, DAY)

)

Let me know if you have any question on this.