Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Time delta for a calculated column, when sorted by time

I have a set of data, each with a timestamp. 

 

I am trying to create a calculated column that, for each row, contains the time delta between that row and it's chronological predecessor, when the data is sorted by time ascending

 

Cenceptually this seems easy - but trying to specify the sort order in the calculated column is proving difficult....any ideas? 

 

 

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Create a new column like

    [timestamp] - maxx(filter(Table, [timestamp] < earlier([timestamp])),[timestamp])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks - almlost: 

       

      My time collumn is called "timestamp" - and contains time data - but I'm getting the error shown in the image above...

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Ok...it looks like I can't create that in a "measure" - it has to be in a new column. But since I am consuming a remote data source, I can't create a new column. I think this means that this is just a limitation of powerBI - unless I am missing something...

         

        It seems that powerBI is potentially a good agregator of myriad data sources, but analysis of those feeds theselves it is a better done at source?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I wondered if this was a limitation powerBI imposes on live streamed data over historic data, the former of which is only available in the latter after 24 hours. 

     

    However, trying that it seems to make no difference to the output. Frustratingly poiwerBI seems to have sacrificed functionbality for accesibility. It is a lot prettier than messing around with the database and scripts directly, but has far more limitations on what ca neasily be achievd - or at least that seems to be the case so far!

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous ,

      Try the following formula:

      Measure = 
IF(
    MAX(events[times tamp]) = MINX(ALL(events),events[times tamp]),
    BLANK(),
    FORMAT( 
        MAX(events[times tamp]) - MAXX(
            FILTER(
                ALL(events),
                events[times tamp] < MAX(events[times tamp])
            ),
            events[times tamp]
        ), 
        "0.00000" 
    )
)

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

      Best Regards,
      Winniz

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

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Did I answer your question? If you can get the correct result, please accept as solution so that users with the same problem can find it quickly.

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

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