Count task duration with Working days, hours and miuntes

[Kopek]

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 :

Opened	Resolved
2017-06-02 08:39:01	2017-11-30 13:51:41
2017-06-07 09:02:52	2017-12-04 08:01:44
2017-06-13 10:50:55	2017-11-30 13:50:53
2017-06-16 12:19:30	2017-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!

[danextian]

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?

[Kopek · 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 !:)

[v-yuezhe-msft]

@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

[Kopek]

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)

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]

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

are you able to help with this ?

Thanks in advance.

[v-yuezhe-msft]

@Kopek,

Please check the following PBIX file.

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

Regards,
Lydia