I'm new to PowerBI Desktop, coming from Excel.
In the query editor, I would like to create a new column in a table with the difference in time/date from one record to the next, by a separate grouping column (device). An example explains it better. Here's the starting point for the data, with one column for the device id, and another for the Date of the event.
Device__ Date
A______ 5/1/2016
B______ 5/1/2016
C______ 5/2/2016
A______ 5/4/2016
B______ 5/5/2016
A______ 5/10/2016
B______ 5/9/2016
C______ 5/12/2016
I would like to group by Device and Sort by Date, then calculate the differences, to make something like this:
Device__ Date _____ Lag
A______ 5/1/2016___(null)
A______ 5/4/2016___3
A______ 5/10/2016__6
B______ 5/1/2016___(null)
B______ 5/5/2016___4
B______ 5/9/2016___4
C______ 5/2/2016__(null)
C______ 5/12/2016 __10
What's the best way to do this in PowerBI query editor?
Thanks for the help!