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 o...
DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    NonprofitWizard 

    This calculation will be done for each user separately?

  • 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 )
    )
)
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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        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 )
    )
)