Forum Discussion

SebastianCostas's avatar
SebastianCostas
Frequent Visitor
3 years ago
Solved

Getting time passed between two dates based on another field

First of all thanks a lot to whoever can help me.

 

The information I need to calculate is in a single table, and I need to get a column with this value for each row.

 

I have Purchase Requisitions with approvers and I need to know how much time each approver took.

 

The details I have are:

 

PR_ID: the purchase requisition ID

PR_Submited: The date and time the PR was submitted to approval (the same for each line for the same PR)

PR_Approver: who approved the PR

PR_Aprroved_Date: The date and time that approver approve that PR

 

Example:

 

PR_IDPR_SubmitedPR_ApproverPR_Approved_DateTime Taken (needed)
101/01/2023 09:00 AMApprover_101/01/2023 11:00 AM2 hours
101/01/2023 09:00 AMApprover_302/01/2023 09:00 AM18 hours
101/01/2023 09:00 AMApprover_201/01/2023 15:00 AM4 hours

 

As you can see I need to compare the approved date to the previous date and it changes based on two fields and they are not in order:

If the Approver for a particular PR has the earliest approved date that would mean it's the first one and needs to calculate the time with the pr_submited, but if it's not the earlies it needs to calculate with the previous approved_date.

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi SebastianCostas 

     

    Please download my sample file to check my solution. 

    I first add an Index column with Power Query, then use below DAX to calculate the spent hours in a calculated column.  

    Time Taken = 
IF (
    'Table'[Index] = 1,
    DATEDIFF ( 'Table'[PR_Submited], 'Table'[PR_Approved_Date], HOUR ),
    DATEDIFF (
        CALCULATE (
            MAX ( 'Table'[PR_Approved_Date] ),
            ALLEXCEPT ( 'Table', 'Table'[PR_ID] ),
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
        ),
        'Table'[PR_Approved_Date],
        HOUR
    )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi SebastianCostas 

     

    Please download my sample file to check my solution. 

    I first add an Index column with Power Query, then use below DAX to calculate the spent hours in a calculated column.  

    Time Taken = 
IF (
    'Table'[Index] = 1,
    DATEDIFF ( 'Table'[PR_Submited], 'Table'[PR_Approved_Date], HOUR ),
    DATEDIFF (
        CALCULATE (
            MAX ( 'Table'[PR_Approved_Date] ),
            ALLEXCEPT ( 'Table', 'Table'[PR_ID] ),
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
        ),
        'Table'[PR_Approved_Date],
        HOUR
    )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • SebastianCostas's avatar
    SebastianCostas
    Frequent Visitor
    3 years ago

    Thank you very much, it worked perfectly, I had to investigate how to create the index column but seeing the attached file was more than enough.