Calculate difference in minutes

[Anonymous]

I wanna know from the Datestamp coulmn see how much minute difference it's between the previous value and the last one.

I tried this formula below but got totaly different value.

Datediff = DATEDIFF(DataView[CommTimestamp];TODAY();minute)

The datestamp look like YYY-MM-DD-HOUR-MINUTE-SECOND

[hnguy71]

Is the column formatted as a Date type?

[Anonymous]

^^

yes it is.

[hnguy71]

Can you provide some sample data and your expected result?

[Anonymous]

So that is that column, new data comes in every 5 min for each sensor and so on, but not alawys (sometime it drops or coming late) so I wanna make a new column to see how much it's difference between previous and the new datestamp by minutes.

[hnguy71]

Try this:

In a calculated column:

TimeDiff = 
var _PreviousTime = CALCULATE(MAX(Table2[CommTimeStamp]), FILTER(Table2, EARLIER(Table2[CommTimeStamp]) > Table2[CommTimeStamp]))
return
IF(ISBLANK(_PreviousTime), 0, DATEDIFF(_PreviousTime, Table2[CommTimeStamp], MINUTE))

[Anonymous]

I get this error:

The syntax for 'DATEDIFF' is incorrect. (DAX(var _PreviousTime = CALCULATE(MAX(DataView[CommTimeStamp]), FILTER(DataView, EARLIER(DataView[CommTimeStamp]) > DataView[CommTimeStamp]))returnIF(ISBLANK(_PreviousTime), 0. DATEDIFF(_PreviousTime, DataView[CommTimeStamp], MINUTE)))).

[hnguy71]

are you copying and pasting correctly?

TimeDiff = 
var _PreviousTime = CALCULATE(MAX(DataView[CommTimeStamp]), FILTER(DataView, EARLIER(DataView[CommTimeStamp]) > DataView[CommTimeStamp]))
return
IF(ISBLANK(_PreviousTime), 0, DATEDIFF(_PreviousTime, DataView[CommTimeStamp], MINUTE))

[Anonymous]

yes here u can see still error

[hnguy71]

I see that you still have a comma right before the DATEDIFF function. Also, double check to see if your CommTimeStamp column is indeed Data Type of DATE/TIME

[Anonymous]

the column are DATE/TIME already. I paste same code u wrote. but i works for u?

[hnguy71]

@Anonymous 

It definitely does.

[Anonymous]

It's because u have another date format? also american? because I have 2019-04-19 13:33:22

[hnguy71]

it shouldn't matter. My original source for the date is exactly the same as yours. I'll upload the sample file for you to view:
https://1drv.ms/u/s!An8CCFsOzw0uc_OWSjBbUOCOgPk

[Anonymous]

now it works after small changes, thanks but is that possible that it gonna only filter from 10 May and after?  where in the code can I write it and how? because the data are huge and it's says ,

There's not enough memory to complete this operation. Please try again later when there may be more memory available.

[hnguy71]

If you're importing your data, you can filter from May 10th onwards. If you're using LiveConnection or from a database you can specify the filter ie,

SELECT * FROM db.MyDatabase WHERE CommTimeStamp >= '10/05/2019'

[Anonymous]

Can I use another instead for EARLIER? because it's earler that cause this memory problem. or do you have anyother soulation?