Forum Discussion

metcala's avatar
metcala
Icon for Helper III rankHelper III
3 years ago
Solved

Total time a post has been vacant between dates

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] ) )
    )
)
eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
3 years ago

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 )