Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have the following code used for my date table"
TASK ID | CREATION_DATE | LAST_UPDATE_DATE |
10493 | 18/DEC/19 | 2020-01-10 14:15:34 |
10493 | 18/DEC/19 | 2020-01-13 15:16:05 |
10493 | 18/DEC/19 | 2020-01-14 17:48:09 |
10493 | 18/DEC/19 | 2020-01-14 17:58:17 |
10493 | 18/DEC/19 | 2020-01-14 17:58:21 |
10494 | 18/DEC/19 | 2020-01-09 16:49:13 |
10510 | 19/DEC/19 | 2019-12-19 10:17:33 |
10510 | 19/DEC/19 | 2019-12-19 14:45:12 |
10510 | 19/DEC/19 | 2019-12-24 10:12:56 |
10510 | 19/DEC/19 | 2019-12-24 12:38:07 |
10510 | 19/DEC/19 | 2019-12-30 09:57:31 |
10510 | 19/DEC/19 | 2020-01-07 10:10:37 |
10510 | 19/DEC/19 | 2020-01-07 13:51:37 |
10510 | 19/DEC/19 | 2020-01-07 13:56:21 |
10510 | 19/DEC/19 | 2020-01-07 13:56:24 |
10511 | 19/DEC/19 | 2019-12-19 10:17:24 |
10511 | 19/DEC/19 | 2019-12-23 19:30:12 |
10511 | 19/DEC/19 | 2019-12-24 10:10:00 |
10511 | 19/DEC/19 | 2019-12-24 12:36:49 |
10511 | 19/DEC/19 | 2019-12-30 09:54:15 |
10511 | 19/DEC/19 | 2020-01-07 10:12:46 |
10511 | 19/DEC/19 | 2020-01-07 13:54:51 |
10511 | 19/DEC/19 | 2020-01-07 13:56:21 |
10511 | 19/DEC/19 | 2020-01-07 13:56:24 |
10512 | 19/DEC/19 | 2019-12-19 10:17:11 |
10512 | 19/DEC/19 | 2019-12-23 11:17:15 |
10512 | 19/DEC/19 | 2019-12-23 11:43:06 |
10512 | 19/DEC/19 | 2019-12-23 11:43:09 |
10523 | 20/DEC/19 | 2019-12-24 12:38:43 |
10523 | 20/DEC/19 | 2019-12-30 09:47:56 |
10523 | 20/DEC/19 | 2020-01-07 10:15:36 |
10523 | 20/DEC/19 | 2020-01-07 11:43:28 |
10523 | 20/DEC/19 | 2020-01-07 11:49:11 |
10523 | 20/DEC/19 | 2020-01-07 14:03:57 |
10523 | 20/DEC/19 | 2020-01-07 14:26:28 |
10523 | 20/DEC/19 | 2020-01-07 14:26:35 |
10524 | 20/DEC/19 | 2019-12-24 12:34:57 |
10524 | 20/DEC/19 | 2019-12-27 16:22:18 |
10524 | 20/DEC/19 | 2019-12-27 16:43:07 |
10524 | 20/DEC/19 | 2019-12-27 16:43:09 |
10526 | 20/DEC/19 | 2019-12-20 16:46:10 |
10526 | 20/DEC/19 | 2019-12-24 12:40:12 |
10526 | 20/DEC/19 | 2019-12-27 16:25:34 |
10526 | 20/DEC/19 | 2019-12-27 16:43:07 |
10526 | 20/DEC/19 | 2019-12-27 16:43:10 |
10526 | 20/DEC/19 | 2020-01-07 17:35:21 |
10526 | 20/DEC/19 | 2020-01-08 10:25:02 |
10526 | 20/DEC/19 | 2020-01-08 17:20:40 |
10526 | 20/DEC/19 | 2020-01-09 09:35:58 |
10526 | 20/DEC/19 | 2020-01-09 10:46:11 |
10526 | 20/DEC/19 | 2020-01-09 10:52:30 |
10526 | 20/DEC/19 | 2020-01-09 10:52:32 |
10527 | 20/DEC/19 | 2019-12-24 12:39:08 |
10527 | 20/DEC/19 | 2019-12-27 16:27:49 |
10527 | 20/DEC/19 | 2019-12-27 16:43:07 |
10527 | 20/DEC/19 | 2019-12-27 16:43:10 |
10528 | 23/DEC/19 | 2019-12-23 17:09:39 |
10529 | 24/DEC/19 | 2019-12-24 12:39:30 |
10529 | 24/DEC/19 | 2019-12-27 16:32:02 |
10529 | 24/DEC/19 | 2019-12-27 16:43:07 |
10529 | 24/DEC/19 | 2019-12-27 16:43:10 |
10530 | 27/DEC/19 | 2019-12-27 12:11:58 |
10532 | 27/DEC/19 | 2020-01-07 10:19:23 |
10532 | 27/DEC/19 | 2020-01-07 14:32:27 |
10532 | 27/DEC/19 | 2020-01-07 16:20:30 |
10532 | 27/DEC/19 | 2020-01-08 17:42:16 |
10532 | 27/DEC/19 | 2020-01-08 17:46:58 |
10532 | 27/DEC/19 | 2020-01-08 17:47:01 |
10534 | 30/DEC/19 | 2020-01-07 10:13:44 |
10534 | 30/DEC/19 | 2020-01-07 14:13:28 |
10534 | 30/DEC/19 | 2020-01-07 14:26:29 |
10534 | 30/DEC/19 | 2020-01-07 14:26:36 |
10535 | 30/DEC/19 | 2020-01-07 10:13:24 |
10535 | 30/DEC/19 | 2020-01-07 14:18:00 |
10535 | 30/DEC/19 | 2020-01-07 14:26:29 |
10535 | 30/DEC/19 | 2020-01-07 14:26:35 |
10536 | 30/DEC/19 | 2020-01-07 10:13:06 |
10536 | 30/DEC/19 | 2020-01-07 14:22:13 |
10536 | 30/DEC/19 | 2020-01-07 14:26:29 |
10536 | 30/DEC/19 | 2020-01-07 14:26:36 |
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.
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:
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
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 ID | Creation Date | Last update date | Duration (Including weekends) | Duration (excluding weekends) |
10493 | 18/DEC/19 | 2020-01-10 14:15:34 | 24 | 18 |
10493 | 18/DEC/19 | 2020-01-13 15:16:05 | 27 | 19 |
10493 | 18/DEC/19 | 2020-01-14 17:48:09 | 28 | 20 |
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
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:
See my attached pbix file.
Best Regards,
Giotto
Works like magic @v-gizhi-msft !
Thank you very much for your help. I will def be using ur approach more often.
@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)
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |