cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Generate Date Difference Calculation Formulas

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
1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

3 REPLIES 3

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!

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.