Forum Discussion

StephenM's avatar
StephenM
Regular Visitor
6 years ago

Time since an event

Hi,

I'm trying to figure out how to calculate the number of minutes/seconds/whatever since an event occurred in my data.  I have a table of time series data (on sample every five minutes), every now and then a column (call it EVENT_IN_PROGRESS) changes value from 0 to 1 indicating that an event has started.  I want to be able to calculate the amount of time that has passed since the column EVENT_IN_PROGRESS was 1.  I can do this in excel quite simply with:

 

if(B3=1,0,D2+C3)

 

Where the B column is the EVENT_IN_PROGRESS column, D column is TIME_SINCE_LAST_EVENT and C column is the amount of time between each data point or row. 

 

I'm hoping to use this data to colour a scatter plot so would like a value for each individual row.  I'm fairly new to power Bi so feel like I'm missing something simple.  From the reading around I have done most people seem to use the EARLIER function to do similar things, but I can't get it to work without using up 32gb of RAM.

 

Thanks for your help

Steve 

Need Help

5 Replies

  • bpsearle's avatar
    bpsearle
    Resolver II
    6 years ago

    Hi Steve

    Can you post a screen shot of the excel example and of your data table please?

    Thanks, Brian

    • StephenM's avatar
      StephenM
      Regular Visitor
      6 years ago

      Hi Brian,

      Below is the screen shot of my excel example (I have hidden about 600 lines between the 5 and the 2955 to show the first event):

       

      The data table in power bi is pretty much the same.  There are a couple of extra columns (I've added an index and some un-related calculated columns).  The data table has just over 100,000 rows

       

      Hopefully that helps but let me know if you need any more info.

       

      Thanks

      Steve

      • bpsearle's avatar
        bpsearle
        Resolver II
        6 years ago

        Hi Steve, sorry for the delay, I've been crazy busy with a project. Do you still need help with this?

        Thanks, Brian