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

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.