Dax measure counting date/time in Status with Direct Query

[Anonymous]

I cannot manipulate this data much because of Direct query so need to use a dax measure. 
I have a Status_History table and if you look at one ID it looks like this:

ID	STATUS	STATUS CHANGE DATE
1	Inspection Due	Tue Jan 10 2017 18:46:53
1	Inspection Pending	Tue Jan 11 2017 6:30:53
1	Submitted For Review	Tue Jan 11 2017 9:34:04
1	Billing	Tue Jan 11 2017 20:22:15

I would like to count the time in minutes only if the record moves from Inspection Due to Inspection Pending, sometimes it can skip Status and I do not want to count those.  The problem, I cannot get the STATUS CHANGE DATE to return the date and time stamp, it will only return a date.  In my STATUS_HISTORY table, the STATUS CHANGE DATE is of data type Date/Time and I have also tried changing to different formats (3/13/2001 1:20:55 pm).  I have tried using MAX, MIN, SELECTEDVALUE, and a few others but everything will only return me the Date not the time as well.
Something like this:

_StatusChangeDate =

CALCULATE(

SELECTEDVALUE( Status_History[StatusChangeDate]),

FILTER (

ALLNOBLANKROW( Status_History[ClaimStatus]),

Status_History[ClaimStatus] = "Initial Contact Due" )

)

If I could figure out this bug I think I could get the logic.

[v-xiaotang]

Hi @Anonymous 

Have you solved your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Try a mesure like

averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),day))

or

averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))

or

Sumx(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))