HELP! Difference between dates in the same column and conditions

[Anonymous]

I need urgent help please, i need to calculate the difference between dates in the same column depending on the status one sample data:

ID	Fecha	Status
1	01/01/2020	Activo
2	01/01/2020	Vacante
1	03/01/2020	Activo
2	03/01/2020	Activo
1	04/03/2020	Vacante
2	04/03/2020	Activo
1	05/03/2020	Vacante
2	05/03/2020	Vacante
1	04/04/2020	Activo
2	04/04/2020	Activo
1	20/05/2020	Activo
2	20/05/2020	Vacante
1	08/06/2020	Activo
2	08/06/2020	Activo

What i need to do is calculate the difference between the last date that status reads "Vacante" and the first next one that reads "Activo" for example in ID = 1 it would be the difference 05 march and 04 april, the other issue that i have is that this combination can happen multiple times over the year for the same ID, ID=2 is an example and for every time that this status shift occurs i need to calculate the difference please im in need of urgent help

[edhans]

See if this works for you:

Date Differences = 
VAR CurrentDate = 
    MAX( 'Sample Data'[Fecha] )
VAR CurrentID =
    MAX( 'Sample Data'[ID ] )
VAR CurrentStatus =
    MAX( 'Sample Data'[Status] )
VAR Result =
    IF(
        CurrentStatus = "Vacante",
        DATEDIFF(
            CurrentDate,
            MINX(
                FILTER(
                    ALL( 'Sample Data' ),
                    'Sample Data'[ID ] = CurrentID
                        && 'Sample Data'[Status] = "Activo"
                        && 'Sample Data'[Fecha] >= CurrentDate
                ),
                'Sample Data'[Fecha]
            ),
            DAY
        ),
        0
    )
RETURN
    Result

[Anonymous]

It Works! but now i need to get the average of those numbers is there a way to obtain it ?

[edhans · Accepted solution]

It depends on how you define average. You can use this:

Total Average = AVERAGEX('Sample Data',[Date Differences])

But that average will include the 0's for the active codes.

This will return the average for just Vacante status.

Total Average =
AVERAGEX(
    FILTER(
        'Sample Data',
        'Sample Data'[Status] = "Vacante"
    ),
    [Date Differences]
)

[Anonymous]

thank you i´ll try this, is there a way to calculate the difference in a calculated column?

[edhans]

Possibly. I try not to use calculated columns, and you didn't specify that in the original request. Since the original answer works, can you mark that as the solution? If you want to redo this entire model as calculated columns you should repost as a new thread with those requirements.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

[Anonymous]

Do i try it as a measure or a calculated column?