Forum Discussion

stevie_westside's avatar
stevie_westside
Icon for Helper I rankHelper I
2 years ago

Cycle Times Date Table and USERELATIONSHIP

I am banging my head against the wall with something that seems simple, but I cannot logic my way to an answer.

  • I have a Date[Date] table
  • I have Order[Recevied Date]
  • I have Order[Shipped Date]
  • I have Orders[Cycle Time-Received_to_Ship]
  • I have made relationships between Date and Recevied Date, and Date and Shipped Date.

Cycle Time =
   DATEDIFF('Orders'[Received Date],'Orders'[Shipped Date],DAY)

Cycle Time AVGX =
CALCULATE(
   AVERAGEX('Orders','Orders'[Cycle Time-Received_to_Ship])
      ,USERELATIONSHIP('Orders'[Received Date],Date[Date])
)

 
Why use just [Received Date] for this relationship, why wouldn't I use [Ship Date]?  I cannot figure out how to setup this relationship in the measure when the calculation is taking into account, 2 different dates, both in a relationship with Date[Date] from the date table.  Please advise.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    stevie_westside , userelationship is needed only for inactive relationships. Check the two attached files. One has an active and inactive relationship. Another one has both inactive relationships and userelationship  is used in two measures

     

    Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

     

  • stevie_westside's avatar
    stevie_westside
    Icon for Helper I rankHelper I
    2 years ago

    Currently I have only 1 active relationship and about 17 other inactive DATE relationships attached to my date table.  There are a lot of dates I'm working with in my dataset.

    In any case, I think I am overthinking this.  If all dates in my dataset have a relationship setup with Date[Date] from my date table, then I should just be able to use the endpoint (end date) of the cycle time as my relationship.

    For example:

    CT Order Completed - Ship = 
    CALCULATE(
        DIVIDE(
            SUM('Orders'[CycleTIme-OrderComplete_to_Ship]),
            [Orders - Shipped],0
        ),
            USERELATIONSHIP(Date[Date],'Orders'[Ship Date])
    )


    I was banging my head against the wall trying to organize my thought process utilizing all 3 dates... but really the endpoint date (in reference to the measure above) is my ship date.   I would use the:
    USERELATIONSHIP(Date[Date],'Orders'[Ship Date])
    because Ship Date is my end point in the cycle time calculation, I wouldn't use Order Complete Date, or any other date (and subsequent relationships)... All I would need to do from here is throw the Date[Date] field on the X axis and let the relationship take over.  
    Do you agree with my thought process here... am I making any sense?