Forum Discussion

NonprofitWizard's avatar
NonprofitWizard
Frequent Visitor
4 years ago
Solved

Calculating a time duration since a previous timestamp, without calculated columns

Hi there.

 

This is my first post - which I'm writing after struggling for many hours to write an efficient measure 🙃. I am a BI developer for a Warehouse, and my task is to calculate the number of seconds that elapse since a previous pallet was loaded to a truck. So how many seconds passed since the last time a user performed a loading transaction.

 

I was able to accomplish this in Excel by adding calculated columns in excel, but I want to avoid calculated columns for this if I can because my data has many other function types other than Loading, millions of rows, plus I would need to have a consultant add them to the model sadly. That calculated column looked like this:

 

=CALCULATE(MAX(Table[date_time]), FILTER(Table, EARLIER(Table[pallet_id]) = Table[pallet_id] && EARLIER(Table[date_time]) > Table[date_time]), Table[function] = "Load")

 

Below is a few minutes of transaction data to show what I'm lookin at. One complication is as you can see, when a pallet is scanned, every sku on the same pallet gets it's own row, with mostly the same date_time stamp.

 

My goal is to be able to have a table of pallet_id's, and the number of seconds that passed. Does anyone have any suggestions how I could calculate this measure in an efficent manner?

 

date_time function user  truck# pallet_id sku

5:58:26 AMLoad109210101a
5:58:26 AMLoad109210101b
5:58:26 AMLoad109210101c
5:58:26 AMLoad109210101d
5:58:26 AMLoad109210101e
5:58:26 AMLoad109210101f
6:01:55 AMLoad109210102a
6:01:55 AMLoad109210102b
6:01:55 AMLoad109210102c
6:01:55 AMLoad109210102d
6:02:01 AMLoad10929103a
6:02:01 AMLoad10929103b
6:02:01 AMLoad10929103c
6:02:01 AMLoad10929103d
6:02:01 AMLoad10929103e
6:02:16 AMLoad10929104a
6:02:16 AMLoad10929104b
6:02:16 AMLoad10929104c
6:02:16 AMLoad10929104d
6:02:16 AMLoad10929104e
6:02:16 AMLoad10929104f
DAX
  • NonprofitWizard's avatar
    NonprofitWizard
    4 years ago

    Thanks for your response Anonymous . This isn't what I'm looking for.


    See the images of my desired result + calculated Column. I want to get to this without any calculated columns, just a measure:

     

     

    The measure I used is simply 

    Seconds Between Loads = DATEDIFF(MAX(Table1[Last Pallet Loaded]), MAX(Table1[date_time]),SECOND)





  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi NonprofitWizard 
    Somehow I missed to answer this query. Aplogies for that.

    Here is a sample file with the proposed solution https://we.tl/t-Rc90TG1vUz

    Measure Tamer = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[user], 'Table'[pallet_id] ),
    CALCULATE (
        VAR CurrentPallet =
            SELECTEDVALUE ( 'Table'[pallet_id] )
        VAR CurrentUserTable =
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[user] ) )
        VAR PreviousPalletsTable =
            FILTER ( CurrentUserTable, 'Table'[pallet_id] < CurrentPallet )
        VAR PreviousPallet = MAXX ( PreviousPalletsTable, 'Table'[pallet_id] )
        VAR PreviousPalletRecord = 
            FILTER ( PreviousPalletsTable, 'Table'[pallet_id] = PreviousPallet )
        VAR CurrentPalletStart =
            MIN ( 'Table'[date_time] )
        VAR PreviousPalletEnd =
            MAXX ( PreviousPalletRecord, 'Table'[date_time] )
        RETURN
            DATEDIFF ( PreviousPalletEnd, CurrentPalletStart, SECOND )
    )
)

9 Replies

    • NonprofitWizard's avatar
      NonprofitWizard
      Frequent Visitor
      4 years ago

      Apoligies, I'm not sure the best way to format the tables... pallet_id's are actually labeled 101 - 104 in my examples, a-f are for the sku.

       

      Like this:

       

      Pallet_ID        Seconds Since Last Load

      102209
      1036
      10415

       

      Note how in the sample data, pallet_id 104 was loaded at 6:02:16 AM, while pallet_id 103 was the most recent pallet loaded 6:02:01 AM. These seconds that elapsed between the pallets was 15.


      For pallet 101 there is no value because it was the first pallet loaded.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  NonprofitWizard ,

    Please refer to my pbix file to see if it helps you.

    Create  a measure.

    Measure =
VAR _min =
    CALCULATE (
        MIN ( 'Table'[date_time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[user] = SELECTEDVALUE ( 'Table'[user] )
                && 'Table'[sku] = SELECTEDVALUE ( 'Table'[sku] )
        )
    )
VAR _next =
    CALCULATE (
        MAX ( 'Table'[date_time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[rankx]
                = SELECTEDVALUE ( 'Table'[rankx] ) - 1
                && 'Table'[sku] = SELECTEDVALUE ( 'Table'[sku] )
        )
    )
VAR _now =
    MAX ( 'Table'[date_time] )
RETURN
    IF ( MAX ( 'Table'[date_time] ) = _min, 0, DATEDIFF ( _next, _now, SECOND ) )

     

    If I have misunderstood your meaning, please provide y more details with your desired output.

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NonprofitWizard's avatar
      NonprofitWizard
      Frequent Visitor
      4 years ago

      Thanks for your response Anonymous . This isn't what I'm looking for.


      See the images of my desired result + calculated Column. I want to get to this without any calculated columns, just a measure:

       

       

      The measure I used is simply 

      Seconds Between Loads = DATEDIFF(MAX(Table1[Last Pallet Loaded]), MAX(Table1[date_time]),SECOND)





      • NonprofitWizard's avatar
        NonprofitWizard
        Frequent Visitor
        4 years ago

        This wasn't the solution, not sure why it's labeled that way