Forum Discussion
Subtract datetime from a column
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 | |
5 Replies
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!
AHi,
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.
thanks but how would i do this in Power BI?
Hi,
Ensure the Excel file is closed. Open PBI desktop and go to File > Import > Excel.
Hope this helps.
