Development of the average dwell time of a project in a specific phase

[Heiner26]

Hi there,

hope, you're all safe and sound 😀

I am trying to show the development of the average dwell time of a project in a respective phases and would be pleased to receive support or at least a hint.

Initial situation:
I have a list of projects that go through certain phases (1&2). What I want to show is how the average dwell time in a respective phase develops. Only projects with the following properties should be taken from the list when calculating it for Phase 1:

• Phase = "1" (-> project is in the respective phase to be calculated)
• Status <> ‘rejected’ (-> project is still ‘live’)
• Date moved out = ‘empty’ (-> project is still in the phase to be calculated)

Project Name	Date moved in	Date moved out	Phase	Status
P1	23/10/2023		1	live
P2	23/11/2023		1	live
P3	01/12/2023		1	rejected
P4	01/12/2023		1	live
P5	04/12/2023		2	live
P6	23/12/2023		1	live
P7	24/01/2024		1	rejected
P8	24/01/2024		1	live
P9	25/01/2024		2	live

Desired result:

In the desired output table the colums should be the dates out of the date tabel (End of each month)

And for the respective gate the average dwell time should be shown. For the examples above the correct values are as follows:

• Oct: 8 days
• Nov: 23 days
• Dec:  36 days
• Jan:  55 days

they are cacluated as follows:

Project	31/10/2023	30/11/2023	31/12/2023	31/01/2024
P1	8	38	69	100
P2		7	38	69
P3	not matching the criteria
P4			30	61
P5	not matching the criteria
P6			8	39
P7	not matching the criteria
P8				7
P9	not matching the criteria
Avg.	8	23	36	55

My approach:

I created this DAX formular using AVERAGEX to calculate the values:

AvgDaysInGate1 =

    AVERAGEX(

        FILTER(

Table,

[Phase]= "1"

&&

[Status]<> "rejected"

&&

[Date moved out]= BLANK()

),

DATEDIFF([Date moved in], MAX(Datetable[Date]),DAY

))

The challenge:

With this DAX formular every project is only used once in the calculation. Or better to say: Project "P1" is included in the calculation for October but isn't for November. For November only "P2" is considered for the calculation - so the value for November I get in my Power BI Table is "7". Same for December: Projects with a "Date moved in" before December are not taken into account:

Does anybody know how I could twich my dax formular to get the right values? 🙏

[Heiner26]

Thank you! This shows exactly what I was looking for 😀🙏

And sure: now there is no need to dwell on it 😉

[Heiner26]

Thank you! This shows exactly what I was looking for 🙏

[lbendlin · Accepted solution]