Calculate average

[Priscila_Gr]

I have a  table date, name_id and name_id_phase. Each day represents a day for a name_id and the respective phase
I would like to calculate the average days in which only the name_id that changed phase.

My objective is to assemble a table according to the image (only considering those that are no longer in the phase).

Here it is a data sample:

Name id	Phase	Date
81266	Phase 3	24-Feb-23
81266	Phase 3	25-Feb-23
81266	Phase 3	26-Feb-23
81266	Phase 3	27-Feb-23
81266	Phase 3	28-Feb-23
81266	Phase 4	2-Mar-23
81266	Phase 4	3-Mar-23
81266	Phase 4	4-Mar-23
82333	Phase 3	24-Feb-23
82333	Phase 3	25-Feb-23
82333	Phase 3	26-Feb-23
82333	Phase 3	27-Feb-23
82333	Phase 3	28-Feb-23
82333	Phase 3	2-Mar-23
82333	Phase 3	3-Mar-23
82333	Phase 3	4-Mar-23
851111	Phase 1	24-Feb-23
851111	Phase 3	25-Feb-23
851111	Phase 3	26-Feb-23
851111	Phase 3	27-Feb-23
851111	Phase 3	28-Feb-23
851111	Phase 3	2-Mar-23
851111	Phase 3	3-Mar-23
851111	Phase 3	4-Mar-23

[MAwwad]

To calculate the average days it takes for a name_id to change phase, you can follow these steps:

1. First, you need to identify the names that changed phases. You can do this by grouping the table by Name id and counting the number of distinct phases. If the count is greater than 1, it means the name changed phase.

2. Next, you can filter the table to include only the rows where the name changed phase.

3. For each name_id that changed phase, you need to calculate the number of days it took to change phase. To do this, you can use the DATEDIFF function to calculate the difference between the dates for the first and last row for each name_id.

4. Finally, you can calculate the average days it took for each name_id to change phase.

Here's the DAX code to accomplish this:

Average Days = VAR ChangedNames = FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Name id], "DistinctPhases", DISTINCTCOUNT ( 'Table'[name_id_phase] ) ), "ChangedPhase", IF ( [DistinctPhases] > 1, 1, 0 ) ), [ChangedPhase] = 1 ) RETURN AVERAGEX ( ChangedNames, DATEDIFF ( CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Name id] = EARLIER ( 'Table'[Name id] ) && 'Table'[name_id_phase] <> EARLIER ( 'Table'[name_id_phase] ) ) ), CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Name id] = EARLIER ( 'Table'[Name id] ) && 'Table'[name_id_phase] <> EARLIER ( 'Table'[name_id_phase] ) ) ), DAY ) )

This code assumes your table is named 'Table', and the columns are named 'Name id', 'name_id_phase', and 'Date'. The result will be the average number of days it took for each name_id to change phase, only considering the names that are no longer in the previous phase.

[Priscila_Gr]

Thank you for your answer.

Unfortunately it returns an error:

A single value for column 'name_id_phase' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any ideas?