Finding the different in a value when a condition from another column is met

[xsaldana]

Hello! I am having trouble finding the difference in unix time between readings at the same location. Below is an example of a table. The "Time in between readings" is the column I am attempting to create. I highlighted the table in terms of location to emphasize that the difference only needs to be taken if at the same location. 

This is the command I have, but it does not work properly. Any suggestions on how to do this?

Time in between readings = IF(ISBLANK(Data[temperature]) = FALSE() && Data[Location = Data[Location], VAR Diff = Data[unix time] - CALCULATE(SUM(Data[unix time]), Filter(Data, Data[Index] = EARLIER(Data[Index]) ) ) 

return 

IF(Diff = VALUE(Data[unix time]), 0, diff))

[tamerj1 · Accepted solution]

@xsaldana 
Another solution with some help from power query (if applicaple) https://www.dropbox.com/t/Gj8t4ZxelU0zRmqA

Time in between readings = 
VAR CurrentIndex = Data[Index]
VAR CurrentTime = Data[unix time]
VAR PreviousTime = 
    CALCULATE ( 
        VALUES ( Data[unix time] ),
        ALLEXCEPT ( Data, Data[Location] ),
        Data[Index] = CurrentIndex - 1
    )
RETURN
    IF (
        NOT ISBLANK ( Data[temperature] ) && NOT ISBLANK ( PreviousTime ),
        CurrentTime - PreviousTime
    )

[xsaldana]

That worked. Thank you!!

[tamerj1]

Hi @xsaldana 

you may try

Time in between readings =
VAR CurrentTime = Data[unix time]
VAR LocationTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) )
VAR PreviousTime =
    MAXX (
        FILTER (
            CurrentTime,
            Data[unix time] < CurrentTime
                && Data[temperature] <> BLANK ()
        ),
        Data[unix time]
    )
RETURN
    IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

[xsaldana]

@tamerj1 

Thank you for suggesting this! Unfortunately, it did not work. It's stuck on "working on it"

[tamerj1]

@xsaldana 
Apparantly this need to be optimized. However, and before going into further complicatios would you please try this

Time in between readings =
VAR CurrentTime = Data[unix time]
VAR LocationTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) )
VAR T1 =
    FILTER ( CurrentTime, Data[unix time] < CurrentTime )
VAR T2 =
    FILTER ( T1, Data[temperature] <> BLANK () )
VAR PreviousTime =
    MAXX ( T2, Data[unix time] )
RETURN
    IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

[xsaldana]

@tamerj1  

Thank you! It seem this one runs but it doesn't work. The error I get is "The FILTER function expects a table expression for argument", but a string or numeric expression was used. 

PowerBI highlights red "CurrentTime" in 

VAR T1 =
    FILTER ( CurrentTime, 

[tamerj1]

@xsaldana 

My mistake, I copied the wrong variable. 

Time in between readings =
VAR CurrentTime = Data[unix time]
VAR LocationTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) )
VAR T1 =
    FILTER ( LocationTable, Data[unix time] < CurrentTime )
VAR T2 =
    FILTER ( T1, Data[temperature] <> BLANK () )
VAR PreviousTime =
    MAXX ( T2, Data[unix time] )
RETURN
    IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

[amitchandak]

@xsaldana , New column

new column =
var _max = max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] < earlier( Data[Location] )),[unix time])
return
IF(not(ISBLANK(Data[temperature])) , Data[temperature] - max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] =_max ), Data[temperature]) , blank())

[xsaldana]

@amitchandak 

Hello, thank you for taking the time to look at this! Unfortunately, I get an error when I use your command. It says "the expression refers to multiple columns. multiple columns cannot be converted to scalar value.