DATEDIFF Meausre Question

[awitt]

Looking to find the time difference betwee certain items within an order. My data set has a different line for each status of each item of each order. What i am looking to do is create a measeure that calculates the time between certain statuses within each of these unique order / item types. In this case, the time betwee the "confirmed" status and the "allocated" status.

I'm sure its a DATEDIFF function but i am having trouble with the filter context. 

[v-juanli-msft · Accepted solution]

Hi @awitt 

1.create two tables based on your table

table 1

status_1 = VALUES(Sheet2[status])

Change [status] in this table to [status_1]

table2

status_2 = VALUES(Sheet2[status])

Change [status] in this table to [status_2]

Add [status_1] and [status_2] in two slicers

Note: don't create any relationships among these two tables with your main table, just leave them alone

2. create measures in your main table

selected1 = SELECTEDVALUE(status_1[status_1])

selected2 = SELECTEDVALUE(status_2[status_2])

date1 = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[order number],Sheet2[item]),Sheet2[status]=[selected1]))

date2 = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[order number],Sheet2[item]),Sheet2[status]=[selected2]))

diff = DATEDIFF([date1],[date2],HOUR)

final = IF([date2]=BLANK(),"N/A",[diff]/24)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.