Measure for number of days between status updates

[kman42]

I have a Status_History table linked to a Proposal table by Proposal_ID. The Status_History table contains the Date and Status for each change. I want to create a measure that calculates the number of days between two specific statuses.

Status_History might look like this:

I want to calculate the number of days between Recieved and Executed statuses and be able to filter across Proposal_ID depending on the context. I think I should use the DATESBETWEEN function, but I'm not sure how to get the dates for the statuses I'm interested in.

Ultimately, I'd like to get the average of this number for all proposals with Execution dates in a certain range (by quarter).

Thanks!

[smpa01]

Measure :=
DATEDIFF (
    CALCULATE (
        MAX ( T[Date] ),
        FILTER ( ALLEXCEPT ( T, T[ID] ), T[Status] = "Received" )
    ),
    CALCULATE (
        MAX ( T[Date] ),
        FILTER ( ALLEXCEPT ( T, T[ID] ), T[Status] = "Executed" )
    ),
    DAY
)

@kman42it might work

[kman42]

When I put the measure into a column of a matrix with Proposals records in the rows, it doesn't seem to filter based on Status_History[Proposal_ID]=Proposals[Proposal_ID]. I get the same result in every row. 

[danextian]

Hi @kman42 ,

Tested @smpa01's formula and it worked just fine. You'll get the same figure for each row propsal ID as you're calculating the day difference between Received and Executed for each ID.
 

[kman42]

I want columns in my table visual from the Proposal table and then a column with the measure, as such:

Right now it looks like:

Here's the code for Measure:

Measure = DATEDIFF (
   CALCULATE (
        MAX(proposal_status_history[created_on_date]),
        FILTER(ALLEXCEPT(proposal_status_history,proposal[proposal_id]),proposal_status_history[proposal_status]="Executed")
),
   CALCULATE (
      MAX (proposal_status_history[created_on_date]),
      FILTER(ALLEXCEPT(proposal_status_history,proposal[proposal_id]),proposal_status_history[proposal_status]="Awarded")
),
DAY
)

[danextian · Accepted solution]

I am not sure if you were referring to measure as calculated column as that's what probably has caused the confusion. Anyway, try this as a calculated column: 

Diff =
VAR __RECEIVED =
    CALCULATE (
        MAX ( 'Table'[Created_On] ),
        ALLEXCEPT ( 'Table', 'Table'[Proposal_ID] ),
        'Table'[Status] = "Received"
    )
VAR __EXECUTED =
    CALCULATE (
        MAX ( 'Table'[Created_On] ),
        ALLEXCEPT ( 'Table', 'Table'[Proposal_ID] ),
        'Table'[Status] = "Executed"
    )
RETURN
    DATEDIFF ( __RECEIVED, __EXECUTED, DAY )

The result should look something like this:

Don't forget to change the names of the tables and column accordingly.

[smpa01]

@kman42  can you please show the desired output.