Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kopek
Helper IV
Helper IV

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

, exactly 🙂

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 !:)

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

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?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

, exactly 🙂

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 !:)
v-yuezhe-msft
Employee
Employee

@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.

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))))

@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.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.