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).

 

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 IDDivisionGradeDisciplineOccupant ID/Vacancy IDStart DateEnd Date

1001

A1.1Sales100011/1/2030/6/21

1001

A1.1SalesV00101/7/2130/9/21

1001

A1.1Sales100101/10/21 
1002B1.3Support100021/1/2031/12/20
1002B1.3SupportV00021/1/2131/1/21
1002B1.3Support100031/2/2131/8/21
1002B1.3SupportV00121/9/2130/9/21
1002B1.3Support100151/10/21 

 

Expected Outcome

 

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

 

Role IDVacant DaysVacant Instances
1001921
1002612

 

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

 

Thanks in advance!

  • 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] ) )
    )
)

3 Replies

  • 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!!

  • 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 )