cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kopek Regular Visitor
Regular Visitor

Count task duration with Working days, hours and miuntes

Hello, I would like to count duration of each task.

The working days only should be counted. What's more counting on full days is not enough, I should count duration in working days including hours and minutes.


Are you able to help me with creating appropriate measue/ function to do it ?

 

My data are as follow :

OpenedResolved
2017-06-02 08:39:012017-11-30 13:51:41
2017-06-07 09:02:522017-12-04 08:01:44
2017-06-13 10:50:552017-11-30 13:50:53
2017-06-16 12:19:302017-12-04 15:19:23

 

Now Working days and hours and minutes should be counted between resolved and opened.

 

Ofc everything is connected to the powerbi.

 

I have also generated calendar where I have marked all working days - "Working day" and Saturdays and Sundays as "Weekend" and relationship between these tables are done.

But, what should be done next?

 

Thanks in advance for your advices!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kopek Regular Visitor
Regular Visitor

Re: Count task duration with Working days, hours and miuntes

, exactly Smiley Happy

Thank you all for your answers, @v-yuezhe-msft I have not had chance to check your second solution , but I found exactly what I need under this link , https://youtu.be/GLIoDbOiJgw, so if you have similar problem in the future, I highly recommend it.

Thanks a lot !Smiley Happy
6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: Count task duration with Working days, hours and miuntes

@Kopek,

Create the following column in your calendar table. Please note that there is no relationship between calendar table and the original table.

Column = IF('calendar'[isworkday]="Working day",1,0)


Create a new table using DAX below.

DatesDuration = ADDCOLUMNS(SELECTCOLUMNS(GENERATE(Table,FILTER(ALLNOBLANKROW('calendar'),AND('calendar'[Date]>=Table[Opened].[Date],'calendar'[Date]<=Table[Resolved].[Date]))),"open",Table[Opened],"resolve",Table[Resolved],"days",'calendar'[Date],"weekday",'calendar'[Column]),"Duration",IF(FORMAT([open],"Short Date")=FORMAT([resolve],"Short Date"),[resolve]-[open],IF(FORMAT([days],"Short Date")=FORMAT([open],"Short Date"),[days]+1-[open],IF(FORMAT([days],"Short Date")=FORMAT([resolve],"Short Date"),[resolve]-[days],1))))


Create the following measure in the new table.

Durationonlyweekdays = CALCULATE(SUM(DatesDuration[Duration]),DatesDuration[weekday]=1)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kopek Regular Visitor
Regular Visitor

Re: Count task duration with Working days, hours and miuntes

Hello @v-yuezhe-msft,

 

Thanks for your help!

 

Unfortunaltely something seems to be wrong in the formula, the problem starts when [days] has to be added to the formula. Can you let me know where from [dates] are taken? is it column I suppose to have ? 

Or has it to be created  from sratch?  (I am attaching formula shreenshot)pbi.jpg

DatesDuration = ADDCOLUMNS(SELECTCOLUMNS(GENERATE(incidents,FILTER(ALLNOBLANKROW('Calendar'),AND('Calendar'[Date]>=incidents[Opened].[Date],'Calendar'[Date]<=incidents[Resolved].[Date]))),"open",incidents[Opened],"resolve",incidents[Resolved],"days",'Calendar'[Date],"weekday",'Calendar'[Column],"duration",IF(FORMAT(incidents[Opened],"short date")=FORMAT(incidents[Resolved],"short date",incidents[Resolved]-incidents[Opened],IF(FORMAT([days], "Short Date")=FORMAT(incidents[Opened], Short Date", [days]+1-incidents[opened], IF (FORMAT [days], "Short Date")=FORMAT(incidents[Resolved], "Short Date"), incidents[Resolved]-[days],1))))

Kopek Regular Visitor
Regular Visitor

Re: Count task duration with Working days, hours and miuntes

@v-yuezhe-msft I found out, that "Days" coulm I created in the measure, I have corrected the measure accordingly, but unfortunately I have received wrong message :  "The Expression Refers to multiple columns. Multiple columns cannot be converted to a scalar value"

 

 

22-03-2018 13-04-51.jpg

 

are you able to help with this ?

 

Thanks in advance.

Moderator v-yuezhe-msft
Moderator

Re: Count task duration with Working days, hours and miuntes

@Kopek,

Please check the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogzCVgOHrAG-64rZZ

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
danextian New Contributor
New Contributor

Re: Count task duration with Working days, hours and miuntes

Hi @Kopek,

 

If I understand correctly, what you are trying to achieve is something like this.

 

  • If start datetime is 11:59 pm Sunday and end is 12:01 AM Monday, the work time should only be one minute.
  • Although there is one workday between the two datetimes as Monday is a workday, the work time shouldn't be counted as one day like NETWORKDAYS in Excel would. So the workday should only be 0.00069444444 which is 1/(60 minutes x 24 hours)

Correct?

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Kopek Regular Visitor
Regular Visitor

Re: Count task duration with Working days, hours and miuntes

, exactly Smiley Happy

Thank you all for your answers, @v-yuezhe-msft I have not had chance to check your second solution , but I found exactly what I need under this link , https://youtu.be/GLIoDbOiJgw, so if you have similar problem in the future, I highly recommend it.

Thanks a lot !Smiley Happy