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] ) )
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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] ) )
    )
)
metcala's avatar
metcala
Icon for Helper III rankHelper III
3 years ago

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