cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Duration in days (working days only) to complete a task and date table

Hi everyone, 

 

I have the following code used for my date table"

 

Date table =
VAR MinYear = YEAR ( MIN ( 'Summary Extract from History Table'[CREATION_DATE]))
VAR MaxYear = YEAR ( MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "ddd" ),
"Weekday number", WEEKDAY( [Date] ),
"Period", FORMAT( [Date], "MMM-YYYY" ),
"Sorting column", FORMAT( [Date], "YYYYMM" ),
"Week number" , WEEKNUM([Date],2),
"Week", "W" & FORMAT( [Date], "WW-MMM-YY" ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)
 
And my data extract looks like the following:
 
TASK IDCREATION_DATELAST_UPDATE_DATE
1049318/DEC/192020-01-10 14:15:34
1049318/DEC/192020-01-13 15:16:05
1049318/DEC/192020-01-14 17:48:09
1049318/DEC/192020-01-14 17:58:17
1049318/DEC/192020-01-14 17:58:21
1049418/DEC/192020-01-09 16:49:13
1051019/DEC/192019-12-19 10:17:33
1051019/DEC/192019-12-19 14:45:12
1051019/DEC/192019-12-24 10:12:56
1051019/DEC/192019-12-24 12:38:07
1051019/DEC/192019-12-30 09:57:31
1051019/DEC/192020-01-07 10:10:37
1051019/DEC/192020-01-07 13:51:37
1051019/DEC/192020-01-07 13:56:21
1051019/DEC/192020-01-07 13:56:24
1051119/DEC/192019-12-19 10:17:24
1051119/DEC/192019-12-23 19:30:12
1051119/DEC/192019-12-24 10:10:00
1051119/DEC/192019-12-24 12:36:49
1051119/DEC/192019-12-30 09:54:15
1051119/DEC/192020-01-07 10:12:46
1051119/DEC/192020-01-07 13:54:51
1051119/DEC/192020-01-07 13:56:21
1051119/DEC/192020-01-07 13:56:24
1051219/DEC/192019-12-19 10:17:11
1051219/DEC/192019-12-23 11:17:15
1051219/DEC/192019-12-23 11:43:06
1051219/DEC/192019-12-23 11:43:09
1052320/DEC/192019-12-24 12:38:43
1052320/DEC/192019-12-30 09:47:56
1052320/DEC/192020-01-07 10:15:36
1052320/DEC/192020-01-07 11:43:28
1052320/DEC/192020-01-07 11:49:11
1052320/DEC/192020-01-07 14:03:57
1052320/DEC/192020-01-07 14:26:28
1052320/DEC/192020-01-07 14:26:35
1052420/DEC/192019-12-24 12:34:57
1052420/DEC/192019-12-27 16:22:18
1052420/DEC/192019-12-27 16:43:07
1052420/DEC/192019-12-27 16:43:09
1052620/DEC/192019-12-20 16:46:10
1052620/DEC/192019-12-24 12:40:12
1052620/DEC/192019-12-27 16:25:34
1052620/DEC/192019-12-27 16:43:07
1052620/DEC/192019-12-27 16:43:10
1052620/DEC/192020-01-07 17:35:21
1052620/DEC/192020-01-08 10:25:02
1052620/DEC/192020-01-08 17:20:40
1052620/DEC/192020-01-09 09:35:58
1052620/DEC/192020-01-09 10:46:11
1052620/DEC/192020-01-09 10:52:30
1052620/DEC/192020-01-09 10:52:32
1052720/DEC/192019-12-24 12:39:08
1052720/DEC/192019-12-27 16:27:49
1052720/DEC/192019-12-27 16:43:07
1052720/DEC/192019-12-27 16:43:10
1052823/DEC/192019-12-23 17:09:39
1052924/DEC/192019-12-24 12:39:30
1052924/DEC/192019-12-27 16:32:02
1052924/DEC/192019-12-27 16:43:07
1052924/DEC/192019-12-27 16:43:10
1053027/DEC/192019-12-27 12:11:58
1053227/DEC/192020-01-07 10:19:23
1053227/DEC/192020-01-07 14:32:27
1053227/DEC/192020-01-07 16:20:30
1053227/DEC/192020-01-08 17:42:16
1053227/DEC/192020-01-08 17:46:58
1053227/DEC/192020-01-08 17:47:01
1053430/DEC/192020-01-07 10:13:44
1053430/DEC/192020-01-07 14:13:28
1053430/DEC/192020-01-07 14:26:29
1053430/DEC/192020-01-07 14:26:36
1053530/DEC/192020-01-07 10:13:24
1053530/DEC/192020-01-07 14:18:00
1053530/DEC/192020-01-07 14:26:29
1053530/DEC/192020-01-07 14:26:35
1053630/DEC/192020-01-07 10:13:06
1053630/DEC/192020-01-07 14:22:13
1053630/DEC/192020-01-07 14:26:29
1053630/DEC/192020-01-07 14:26:36
 
I have also formated the dates column in the same format and removed the time stamp also.
 
I have several questions with regards to the date table:
 
1) is my syntax for the variable correct; 
VAR MinYear = YEAR ( MIN ( 'Summary Extract from History Table'[CREATION_DATE]))
VAR MaxYear = YEAR ( MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE]) )
In other words, can i used two different date column to build my date table ? or should i use the min and max from the same column ? for e.g Creation date column ?
 
 
2) I have an issue with the date table as i cant get the weekdays to start on Monday ( monday equal to one) as you can see on the screen shot below. Does this affect my calcuation as i am trying to calculate the average duration time it takes to complete a task from Creation date to Last update date?
 
Capture.JPG

 

3) Last, i am trying to calculate the the average duration time in days excluding weekends it takes a task to complete ( start date = creation date & end date = Last update Date). i have no idea how to set upmy date table/model to help me achieve this calculation and i would like ur input about the different ways i can handle this with.
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Duration in days (working days only) to complete a task and date table

Hi,

 

Please try these two measures:

Duration (Including weekends) = 
DATEDIFF (
    MAX ( 'Summary Extract from History Table'[CREATION_DATE] ),
    MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE] ),
    SECOND
) / ( 3600 * 24 )
Duration (Excluding weekends) = 
[Duration (Including weekends)]
    - CALCULATE (
        COUNT ( 'Date table'[Date] ),
        FILTER (
            'Date table',
            'Date table'[Weekday number] in {6,7}
                && 'Date table'[Date]
                    >= MAX ( 'Summary Extract from History Table'[CREATION_DATE] )
                && 'Date table'[Date]
                    <= MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE] )
        )
    )

The result shows:

10.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Duration in days (working days only) to complete a task and date table

@Mous007 , In this file, check how I calculated working days

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

 

You might have to change the working day calculation

like

if(WEEKDAY('Date'[Date],2)>6,0,1)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Duration in days (working days only) to complete a task and date table

Hi @amitchandak , unfortunately i cannot download files on this computer and only have an older version of power bi.

 

Would you mind copying the dax directly in here please ?

 

Thanks in advance

Highlighted
Super User IV
Super User IV

Re: Duration in days (working days only) to complete a task and date table

@Mous007 ,

This is for working days

Working Days = CALCULATE(Sum('Date'[Working day]),VALUES('Order Dim'[Order No]),filter(all('Date'),'Date'[Date]>=[Min Order Date OD] && 'Date'[Date] <=[Max Deilvery OD]))

 

Rest is given here

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Duration in days (working days only) to complete a task and date table

Hi,

 

For your issue 1and 2, please try this calculated table:

Date table = 
ADDCOLUMNS (
CALENDARAUTO( ),
"Calendar Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "ddd" ),
"Weekday number", IF(WEEKDAY( [Date] )=1,7,WEEKDAY([Date])-1),
"Period", FORMAT( [Date], "MMM-YYYY" ),
"Sorting column", FORMAT( [Date], "YYYYMM" ),
"Week number" , WEEKNUM([Date],2),
"Week", "W" & FORMAT( [Date], "WW-MMM-YY" ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)

The result shows:

5.PNG

And i am confused about your requirement for issue 3, could you please share the expected result as as a screenshot?

This will let me help you further.

Expect your reply!

 

Best Regards,

Giotto

Highlighted
Helper III
Helper III

Re: Duration in days (working days only) to complete a task and date table

Hi @v-gizhi-msft , thank you for you reponse. My date table looks correct now.

 

For the remaining question, I simply want to compute the duration in days (working days only) for a task to be completed. meaning calcuate the duration between CREATION DATE & LAST UPDATE DATE on the table above. My issue is to get the calculation correctly with working days only (excluding weekends) so i can later on compute the average processing time for all requests by specific period.

 

For example, the calculation for the first three requests on the table should be almost similar to the following:

 

Ticket IDCreation DateLast update dateDuration (Including weekends)Duration (excluding weekends)
1049318/DEC/192020-01-10 14:15:342418
1049318/DEC/192020-01-13 15:16:052719
1049318/DEC/192020-01-14 17:48:092820

 

I am more interested in calculating the last column: Duration without weekends.

 

Please advise on how i should proceed in this case ?

 

Thanks in advance

Highlighted
Community Support
Community Support

Re: Duration in days (working days only) to complete a task and date table

Hi,

 

Please try these two measures:

Duration (Including weekends) = 
DATEDIFF (
    MAX ( 'Summary Extract from History Table'[CREATION_DATE] ),
    MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE] ),
    SECOND
) / ( 3600 * 24 )
Duration (Excluding weekends) = 
[Duration (Including weekends)]
    - CALCULATE (
        COUNT ( 'Date table'[Date] ),
        FILTER (
            'Date table',
            'Date table'[Weekday number] in {6,7}
                && 'Date table'[Date]
                    >= MAX ( 'Summary Extract from History Table'[CREATION_DATE] )
                && 'Date table'[Date]
                    <= MAX ( 'Summary Extract from History Table'[LAST_UPDATE_DATE] )
        )
    )

The result shows:

10.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

Highlighted
Helper III
Helper III

Re: Duration in days (working days only) to complete a task and date table

Works like magic @v-gizhi-msft !

 

Thank you very much for your help. I will def be using ur approach more often.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors