Forum Discussion

awitt's avatar
awitt
Helper III
7 years ago
Solved

DATEDIFF Meausre Question

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. 

 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.