Generate Date Difference Calculation Formulas

[Syndicate_Admin]

Good afternoon, how are you? I have a question in Power bi.

I have the following problem: I have a data table, where I have a "Project" column, an "Initial Status" column, and a "Result Status" column. When a project is created, an identifier is assigned, and it is set to "Created" in the initial state, and when the project is submitted, it is set to "Submitted" in the result state.

I need to calculate the average number of days between when the project is created and when it is submitted.

I don't know how to calculate the delay of days per project, then do the sum and divide by the number of projects. Can you help me? Thank you!

Project	Initial Status	Estado Result.	F. This. Tarea	F. Real Purpose
INFM-23016	In Estimation	In Approval	31/1/2023	1/2/2023
INFM-23016	Created	Unassigned	31/1/2023	31/1/2023
INFM-23016	Unassigned	In Estimation	31/1/2023	31/1/2023
INFM-23016	In Estimation	In Review	31/1/2023	9/2/2023
INFM-23016	Created	In Estimation	31/1/2023	31/1/2023
INFM-23016	In Approval	100% Progress	1/2/2023	9/2/2023
INFP-21591	In Planning	In Approval	16/3/2023	16/3/2023
INFP-21591	In Approval	Approved	16/3/2023	16/3/2023
INFM-23016	In Review	In Planning	9/2/2023	13/2/2023
INFP-21591	Envoy	Overcome	15/3/2023	15/3/2023
INFP-21591	In Review	In Planning	15/3/2023	16/3/2023
INFP-21591	Created	Envoy	20/10/2022	24/10/2022
INFM-23016	In Planning	In Approval	13/2/2023	13/2/2023
INFP-21591	Approved	Envoy	16/3/2023	17/3/2023
INFM-23016	In Approval	Approved	13/2/2023	14/2/2023
INFF-23103	Created	In Estimation	3/7/2023	3/7/2023
INFM-23016	Approved	Envoy	14/2/2023	15/2/2023

[Syndicate_Admin]

Thank you very much, if that's what I want. I forgot to request that it be difference of working days, Saturday and Sunday is not counted. What would it be like? With networkdays?.

Thanks a lot!

[ryan_mayu · Accepted solution]

@Syndicate_Admin 

then you need to create a date table.

Table 2 = CALENDAR(min('Table'[F. This. Tarea]),max('Table'[F. Real Purpose]))

and create a column

isworkday =

VAR _weekday=weekday('Table 2'[Date],1)

return if(_weekday=1 ||_weekday=7,0,1)

then create measures

new_days =

VAR _min=min('Table'[F. This. Tarea])

var _max=max('Table'[F. Real Purpose])

return sumx(FILTER('Table 2','Table 2'[Date]>=_min&&'Table 2'[Date]<=_max),'Table 2'[isworkday])

new_days2 = [new_days]/DISTINCTCOUNT('Table'[Project])

pls see the attachment below

[ryan_mayu]

@Syndicate_Admin 

is this what you want?

days = DATEDIFF(min('Table'[F. This. Tarea]),max('Table'[F. Real Purpose]),DAY)

days2 = sumx(VALUES('Table'[Project]),[days])

 

average = [days2]/DISTINCTCOUNT('Table'[Project])

pls see the attachment below