Weekday date difference with Filter Task

[romovaro]

HI Power BI community

I have the table below:

CID	INSTANCE_NUMBER	TASK	TASK_ASSIGNED_ON	TASK_STATUS	TASK_COMPLETED_DATE	TASKACTUALPERFORMER	PRODUCT_DESCRIPTION	CUID
1000543	1229280	Partner Acknowledgement - 1225829 - 1000543FR01	27-JAN-22 04.22.27.000000 AM	Closed	27-JAN-22 09.15.05.529000 AM	John Smith	CSL	1000543FR01
1000402	1207201	Partner Acknowledgement - 1204055 - 1000402GB01	26-JAN-22 10.52.33.000000 PM	Closed	28-JAN-22 01.09.36.999000 PM	John Smith	CSL	1000402GB01
10287	1244225	Partner Acknowledgement - 1243686 - 381322	26-JAN-22 05.34.20.000000 PM	Closed	27-JAN-22 04.19.14.126000 PM	John Smith	SL	381322
1000421	1211610	Partner Acknowledgement - 1207771 - 1000421AT02	26-JAN-22 11.36.11.000000 AM	Closed	08-FEB-22 12.30.32.453000 PM	John Smith	CSL	1000421AT02
1000421	1211609	Partner Acknowledgement - 1207771 - 1000421AT01	26-JAN-22 11.32.12.000000 AM	Closed	08-FEB-22 12.22.54.029000 PM	John Smith	CSL	1000421AT01
1000421	1211625	Partner Acknowledgement - 1207771 - 1000421LU02	26-JAN-22 11.29.05.000000 AM	Closed	30-JAN-22 03.16.14.050000 PM	John Smith	CSL	1000421LU02
1000421	1211624	Partner Acknowledgement - 1207771 - 1000421LU01	26-JAN-22 11.25.23.000000 AM	Closed	30-JAN-22 03.16.14.050000 PM	John Smith	CSL	1000421LU01
1000421	1211639	Partner Acknowledgement - 1207771 - 1000421CH01	26-JAN-22 11.17.20.000000 AM	Closed	10-FEB-22 07.25.01.194000 AM	John Smith	CSL	1000421CH01
1000421	1211593	Partner Acknowledgement - 1207771 - 1000421BR01	26-JAN-22 11.13.27.000000 AM	Closed	01-FEB-22 04.04.15.410000 PM	John Smith	CSL	1000421BR01
1000421	1211640	Partner Acknowledgement - 1207771 - 1000421TR01	26-JAN-22 10.59.13.000000 AM	Received		John Smith	CSL	1000421TR01
1000421	1211635	Partner Acknowledgement - 1207771 - 1000421RU01	26-JAN-22 10.55.54.000000 AM	Closed	10-FEB-22 12.48.44.431000 PM	John Smith	CSL	1000421RU01
1000421	1211622	IPM HandOff	26-JAN-22 10.52.20.000000 AM	Closed	04-FEB-22 06.57.13.616000 AM	John Smith	CSL	1000421KZ01
1000421	1211614	Partner Acknowledgement - 1207771 - 1000421FI01	26-JAN-22 10.46.13.000000 AM	Received		John Smith	CSL	1000421FI01
11328	1240718	Greenlight Checks	26-JAN-22 10.43.12.000000 AM	Closed	27-JAN-22 10.54.27.766000 AM	John Smith	SL	381319
11328	1240717	Partner Acknowledgement - 1240496 - 381318	26-JAN-22 10.42.31.000000 AM	Closed	27-JAN-22 10.54.27.810000 AM	John Smith	SL	381318
12584	1247787	Partner Acknowledgement - 1247808 - 381329	26-JAN-22 09.00.36.000000 AM	Closed	26-JAN-22 09.11.35.502000 AM	John Smith	SL	381329
380278	1233207	Partner Acknowledgement - 1230324 - 380278PH01	26-JAN-22 08.57.02.000000 AM	Closed	02-FEB-22 10.13.09.703000 AM	John Smith	CSL	380278PH01

I need to create some calculations:

I already have the diff in days but I am struggling to connect the formula with the task and get the diff only for the task 

1. To calculate DURATION of a task (how long it took to complete) : 

I need to select TASK = "Partner Acknowledgement" and Task_status = CLosed and calculate the diff in days between task completed and taks assigned (But ONLY COUNTING WEEKDAYS)

2. To calculate AGEING of OPEN tasks (how long they have been pending) :

I need to select TASK = "Partner Acknowledgement" and Task_status = Received and calculate the diff in days between task completed and taks assigned (But ONLY COUNTING WEEKDAYS)

3. To calculate Between Task1 and task 2 :

I need to select

TASK = Greenlight Checks" and Task_status = Closed

&

TASK = IPM HandOff and Task_status = Closed

and use the Task COmpleted Date column to see the average time between Greenlight & IPM handoff (where pre-engagment task closed by Pre team) (if possible Weekdays)

[amitchandak]

@romovaro ,

Example to get workday between two dates as measure or column

measure =

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

column

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

for open you can use today

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

[romovaro]

Thanks Amitchandak,

Regarding the Column formula. Tried to add the formula in my table but it seems that some of my end dates are blank and I get the error that "The start Date or end date in calendar function can not be Blank Value". Something that can be fixed?

I tried the measure formula but it seems I get the total instead of the average. I don't see the option for the average.

[Anonymous · Accepted solution]

HI @romovaro,

You can try to add an if statement into the expression and use today function as the default value to replace the blank values:

Work Day =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                Table[Start Date],
                IF ( Table[End Date] <> BLANK (), Table[End Date], TODAY () )
            ),
            "WorkDay", IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 )
        ),
        [WorkDay] = 1
    )
)

Regards,

Xiaoxin Sheng

[romovaro]

Hi Xiaoxin,

I have another question regarding comparing dates. In this case, my Date column is only one (completed Date Column)

Table is the same one as the first post.

3. To calculate Between Task1 and task 2 :

I need to select

TASK = Greenlight Checks" and Task_status = Closed and see the date in the Completed Date column

&

TASK = IPM HandOff and Task_status = Closed and see the date in the Task Completed Column.

Then I need to calculate the average days between both dates. 

In that case, the start date and End Date is the same column....

Work Day =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                Table[Start Date],
                IF ( Table[End Date] <> BLANK (), Table[End Date], TODAY () )
            ),
            "WorkDay", IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 )
        ),
        [WorkDay] = 1
    )
)

[romovaro]

Thanks XiaoXin, it works 

I have another question regarding some formulas I used in another question. In case you have time to check.

https://community.powerbi.com/t5/Desktop/Review-Custom-formulas/m-p/2369505