Time delta for a calculated column, when sorted by time

[Anonymous]

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? 

[Anonymous]

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]

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]

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.

[amitchandak]

@Anonymous , Create a new column like

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

[Anonymous]

Thanks - almlost: 

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

[Anonymous]

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?