DATEDIFF based on specific value

[Anonymous]

Hello,

I am creating a report that works out the transit time of an order from one place to another (like a courier service). This report uses milestones, each with a date attached, for example, ARV (Arrived at destination), DEP (Departed origin) and DCF (Delivered at destination). 

For each order several milestones will apply, in this case, the first image shows how the rows relate to one order (JobOrderHeader.JD_OrderNumber). The second image is a continuation of the first (same table), I am needing to work out the difference between the milestones (P9_SE_NKMilestoneEvent) ARV and DCF, the dates are in the column labeled P9_ActualDate.

ARV = 04/10

DCF = 06/10 the difference is 2 days.

Does anyone have any ideas how to achieve this?

Thank you 

[amitchandak]

The solution @Greg_Deckler , will work. You just need to make sure of the row context. You need to group the same kind Id together.

Refer this blog : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

Data need to group at some level for date diff in measure. Else you can try other solutions from Greg to use earlier and create a new column. Play with the condition as per need

something like
timediff = datediff(table[Time],maxx(filter(table,table[Id] =earlier(table[Id] && table[date] <earlier(table[date]) && table[event] ="ARV" ),table[Time]),day)

[V-lianl-msft]

Hi @Anonymous ,

You could try to "unpivot" the column.

If the problem persists,could you please share sample pbix?(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, assuming you have JobOrderHeader.ID_OrderNumber in something like a table visualization you could do something like this:

Measure =
  DATEDIFF(
    MAXX(FILTER('Table',[P9_SE_NKMilestoneEvent] = "ARV"),[P9_ActualDate]),
    MINX(FILTER('Table',[P9_SE_NKMilestoneEvent] = "DCF"),[P9_ActualDate]),
    DAYS
  ) + 1