Total time a post has been vacant between dates

[metcala]

Hi

I am looking to create a DAX measure to calculate the total amount of time a post has been vacant between two dates and another measure to count the instances of vacancy (recruitment campaigns).

All Occupant IDs start with a number and all Vacancy IDs start with V.

The data structure certainly is not ideal but unfortunately this is something I don't have control of and can not change.

Data Structure

Role ID	Division	Grade	Discipline	Occupant ID/Vacancy ID	Start Date	End Date
1001	A	1.1	Sales	10001	1/1/20	30/6/21
1001	A	1.1	Sales	V0010	1/7/21	30/9/21
1001	A	1.1	Sales	10010	1/10/21
1002	B	1.3	Support	10002	1/1/20	31/12/20
1002	B	1.3	Support	V0002	1/1/21	31/1/21
1002	B	1.3	Support	10003	1/2/21	31/8/21
1002	B	1.3	Support	V0012	1/9/21	30/9/21
1002	B	1.3	Support	10015	1/10/21

Expected Outcome

Date filter 1/1/20 to 30/9/21

Role ID	Vacant Days	Vacant Instances
1001	92	1
1002	61	2

Have been struggling with this one and any help would be very much appreciated!

Thanks in advance!

[eliasayyy]

hello @metcala 

use 3 measures

Condition = 
IF( LEFT(MAX('Table'[Occupant ID/Vacancy ID]),1) = "V" , "Yes" , "No")

Count = 
COUNTROWS(FILTER('Table',[Condition] = "Yes"))

Date Diff = SUMX(FILTER('Table',[Condition] = "Yes"),DATEDIFF( 'Table'[Start Date] , 'Table'[End Date] ,DAY) + 1 )

[tamerj1 · Accepted solution]

Hi @metcala 

Please refer to attached sample file with the proposed solution

Vacant Days = 
VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        FILTER ( 
            'Table',
            ISERROR ( VALUE ( 'Table'[Occupant ID/Vacancy ID] ) )
        ),
        DATEDIFF (
            MAX ( 'Table'[Start Date], MinDate ),
            MIN ( COALESCE ( 'Table'[End Date], TODAY ( ) ), MaxDate ),
            DAY
        ) + 1
    )

Vacant Instances = 
COUNTROWS ( 
    FILTER ( 
        'Table',
        ISERROR ( VALUE ( 'Table'[Occupant ID/Vacancy ID] ) )
    )
)

[metcala]

Thanks so much for the prompt response. It worked perfectly!!