Home Forums Power Pivot Difference between Dates In the Same Column

Tagged: 

This topic contains 12 replies, has 2 voices, and was last updated by  tomallan 2 years, 4 months ago.

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #5855

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi,

    I have a data dump from Oracle that shows the process of a Financial approval process, the ‘Action Date’ is all in one column. Sometimes there could be a number of different actions until it is approved. At the moment I can find the overall lead time of the process but how can I get the time between individual steps if the dates are all in the same column? Each process has a Unique ref number and a process sequence number. Any help would be greatly appreciated.

     

     

    Attachments:
    You must be logged in to view attached files.
    #5857

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    One way to proceed is with a couple of calculated columns to capture dates for previous and next sequence step that belong to the same process. Often done with Functions like EARLIER, FILTER and TOPN. I did not notice a column in the image that would identify the specific process instance.

    Can you attach a workbook with anonymized data, listing a couple of processes and their sequence numbers?

    Tom

    #5858

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi Tom

    Attached is a cut from my data, there are two separate processes with a unique ID. If you have anymore questions please let me know.

    Thanks

     

    Attachments:
    You must be logged in to view attached files.
    #5867

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Samuel,

    Attached workbook used calculated columns to figure out Lead and Lag days for each task.

    Lead days = days between previous action date to “current” row action date.
    Lag days = days between “current” row action date and next action date.

    Attachments:
    You must be logged in to view attached files.
    #5877

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Brilliant Tom!

    Thanks for the help.

    Sam

    #5879

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Samuel,

    You are welcome!

    Please also tell a friend about the PowerPivotPro forums!

    Tom

    #5895

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi Tom,

    The calculations work when transferred into my data model, the only issue is when I try to refresh the linked table I get an error around a circular dependency? Is there a way around this?

    I know in powerpivot you can have issues running calculations on calculated columns. I have attached a screen shot of the error.

     

    Regards

    Sam

    Attachments:
    You must be logged in to view attached files.
    #5899

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Sam,

    Are you using Excel 2010, 2013 or 2016?

    Tom

    #5919

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Sam,

    Have not heard back from you. Were you able to resolve the issue? If not, your version will make a difference in my answer.

    Tom

    #5920

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Morning Tom,

     

    My email failed to send for some reason last night. I am using excel 2016.

    Cheers

    Sam

    #5941

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Sam,

    Sometimes that error message arises with calculated columns when the Power Pivot formula engine does not see a unique row identifier in a single column.

    I have replaced all calculated columns with measures after importing the data through Power Query (Get & Transform group on the Data tab of the Excel ribbon). In Power Query I created a unique row identifier ([Action ID]) in a single column, which is a concatenation of Org Code, Req No and Apr Seq No. In addition, I added another column (Request ID) that is a concatenation of Org Code and Req No.

    Power Pivot in Excel 2016 allows for use of variables, which makes writing many complex formulas less of a chore.

    Please feel free to ask any questions regarding how the measures were put together.

    One important item: because of the way I have implemented totals for Lag and Lead, it will be important that a column like [Action ID], a unique row identifier (primary key) is built into the table. The column does not have to have the same format (a concatenated string), but it needs to be one column with a unique value in every row.

    Tom

    Attachments:
    You must be logged in to view attached files.
    #6077

    Samuelcurr1e
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi Tom,

     

    Apologies for the late reply! Thanks for the help, really appreciate it. I have the model working perfectly now and the linked table refreshes with no issues.

     

    Kind Regards

    Sam

    #6083

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Sam,

    Thanks for following up. Glad that there are no more issues when refreshing.

    If there is an opportunity to do so, please tell a friend about the PowerPivotPro forums!

    Looking forward to working with you again,

    Tom

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic.