Subtract datetime from a column

[saanah2019]

Hi, so I have a datetime field in a column and I just want to create a new column/measure which subtracts the previous entry if the dates are the same.

I have time and I want to calculate the difference in mins.

time	difference (mins)
1/1/2018 5:00
1/1/2018 5:30	30
1/1/2018 6:00	30
1/4/2018 6:00
1/4/2018 6:40	40
1/6/2018 6:00
1/7/2018 6:00
1/7/2018 7:00	60
1/7/2018 7:30	30
1/7/2018 8:05	35

[Ashish_Mathur]

Hi,

Try these calculated column formulas:

Date = INT(Data[time])

Diff = if(ISBLANK(CALCULATE(MAX(Data[time]),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[time]<EARLIER(Data[time])))),BLANK(),Data[time]-CALCULATE(MAX(Data[time]),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[time]<EARLIER(Data[time]))))

Hope this helps.

[saanah2019]

thanks but how would i do this in Power BI?

[Ashish_Mathur]

Hi,

Ensure the Excel file is closed.  Open PBI desktop and go to File > Import > Excel.

Hope this helps.

[ChandeepChhabra]

@saanah2019 

You can do this using Power Query

You can refer to solution in the excel file here

[Anonymous]

hi @saanah2019 

Here is an example or two how to refer to previous record in your table.

https://community.powerbi.com/t5/Desktop/Reference-Previous-Value-in-adjacent-column/td-p/406875

https://community.powerbi.com/t5/Desktop/Reference-previous-row-to-get-value/td-p/154824

Good Luck!
A