Forum Discussion

heiligbd's avatar
heiligbd
Icon for Helper I rankHelper I
3 years ago

Solve For Previous Transaction Req # W/ Conditions to Find DateDiff Same Column

Good day, this is my first time posting and I am struggling with this particular query.  I have this dataset pictured below in PowerBi which I have ranked by Dest Date&Time grouped by batch. I need to be able to match the previous transaction Req No to the next transaction blank where the product is returned to storage. There can be multiple events of this happening with the same batch hence why I have them ranked by date/time within a particular batch. The info to the right in the image is the just of what I am needing. In the image I have shown that I need to solve for the yellow blanks with the above Req No. 

 

My ultimate goal is to match the req no with the production order with its return to storage transaction and thereby find the difference in time between the two transactions and metric it. However, I don't think I can find the time difference without first creating a unique req no that matches the next transaction.

 

Any help is much appreciated!

 

Sample of Data Table

 

Need Help
Tutorial Requests

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try the below as a calculated column

    Previous req no =
IF (
    ISBLANK ( 'Table'[Req no] )
        && 'Table'[Applicable move] = "Good"
        && 'Table'[Movement type] = "Return to storage",
    VAR currentBatch = 'Table'[Batch]
    VAR currentRank = 'Table'[Rank]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE (
                TOPN ( 1, 'Table', 'Table'[Rank] ),
                REMOVEFILTERS ( 'Table' ),
                TREATAS (
                    { ( currentBatch, currentRank - 1, "Good", "Production" ) },
                    'Table'[Batch],
                    'Table'[Rank],
                    'Table'[Applicable move],
                    'Table'[Movement type]
                ),
                NOT ISBLANK ( 'Table'[Req no] )
            ),
            "@val", 'Table'[Req no]
        )
)
  • heiligbd's avatar
    heiligbd
    Icon for Helper I rankHelper I
    3 years ago

    Thanks for the quick response!

    So, I input the query and it didn't fail, but it only returned blanks for all rows instead of any data from Req No.

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      can you share some sample data, either as an excel or as a table that can be copied ?

  • heiligbd's avatar
    heiligbd
    Icon for Helper I rankHelper I
    3 years ago

    Hi, unfortunately, I don't believe I am able to share a file. Apologies. Any additional help you can provide is much appreciated.