Userelationship

[Anonymous]

Hello  -  I am trying to use  userelationship to connect an inactive field.    Ultimately I want to determine the days difference between when orders were due, and when they shipped.  

I have two fields, both in the same table (Orders).    Both connected to a date table   -   one is active (due date) and one is inactive  (date shipped).   

Due Date vs Actual Ship = CALCULATE(DATEDIFF(USERELATIONSHIP('Date Table'[Date],Orders[Date Shipped]),XXXXX

I am trying to add the Due Date field (where the XXXXX's are), but this field does not show as an option.

I really need to have the "date shipped" relationship activated so that I can use the date table month,year, etc to control the slicers and visuals.

[amitchandak]

@Anonymous ,  Date diff refer to this blog

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Or this file, depending on where two dates are there are 4-5 method

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

@amitchandak  Appreciate that file.    However, none of those methods solve my issue.  

I am specifically needing to use  USERELATIONSHIP in order to get both the Due Date field (active), and the Date Shipped field (inactive) to respond to the date table.    

The issue as stated below is that I do not get the option (no intellisense) to use my Orders(due date) field.   It just does not come up as an option when writing the measure.      I'm guessing it has something to do with the fact that it is the active relationship field.  

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

I think what I am asking is fairly straightforward.  

Trying to use a DATEDIFF formula to acheive the days difference between two date fields that are on the same table.    One field is active between the date table, the other is inactive.    Due Date is active.    Date Shipped is inactive. 

When I try to write the measure, the Orders(Due Date) field does not appear in the formula bar so I cannot select it.    

[Anonymous]

@amitchandak        Essentially, here is the measure that I am trying to write.   I realize this is not at all correct, but I need be able to subtract these dates from each other.    And the Orders (Due Date) does not show up in my formula bar unless I put something like Userelationship in front of it  (even thought it is the active relationship).  

[amitchandak · Accepted solution]

@Anonymous , Both date are from the same table. So you do not need userelation for date diff.

In a new column = datediff(order[due Date], order[Shipped Date],DAY)

In a new measure= datediff(min(order[due Date]), Max(order[Shipped Date]),DAY) // Refer my blog for row context

Now use relation is which date you want to use when you display this. So on top of this formula, you can use one of the date.

Or can have two formula one for each date.