Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Formula

Hello everyone,

 

I need to recover the longitude referring to the previous time stamp

 

Here is an example of my data :

Identifiant  PositionTimeStampp OldPositionTimeStamp Long Lat OldLong OldLat
121/07/2022 14:50:00 21/07/2022 14:40:00 0,12928 46,80856 0,13119 46,80699
121/07/2022 14:40:00   0,13119 46,80699    
221/07/2022 13:50:00 21/07/2022 13:40:00 0,09237 47,25609 0,09208 47,25606
221/07/2022 13:40:00   0,09208 47,25606    

 

I did recover the old timestamp (see formula below). Now I wish I could recover and display the old longitude and the old latitude (value red)

 

My formula for timestamp is:

 

oldPositionTimeStamp = CALCULATE(MAX('TABLEPOSITION'[PositionTimestamp]),'TABLEPOSITION'[PositionTimestamp] < EARLIEST('TABLEPOSITION'[PositionTimestamp]),ALLEXCEPT('TABLEPOSITION','TABLEPOSITION'[IDENTIFIANT]))

 

 

I tried this formula to get the old longitude ans the same for the latitude :

 

var OldLong = CALCULATE(MAX('TABLEPOSITION'[Long]), 'TABLEPOSITION'[positionTimestamp] = oldPositionTimeStamp)
return previouslong

 

But I don’t get value in the column, do you have a solution ?

 

Best regards

Need Help
  • PC2790's avatar
    PC2790
    4 years ago

    Hey Anonymous ,

     

    Please try this code to create new calculated column:

     

    requiredLatValue =
VAR oldtimestamp =
    CALCULATE (
        MAX ( 'MyTable'[PositionTimestampp] ),
        MyTable[PositionTimeStampp] < EARLIEST ( MyTable[PositionTimeStampp] ),
        ALLEXCEPT ( 'MyTable', MyTable[Identifiant  ] )
    )
RETURN
    (
        CALCULATE (
            MAX ( MyTable[Lat] ),
            FILTER ( MyTable, MyTable[PositionTimeStampp] = oldtimestamp )
        )
    )

    requiredLongValue =
VAR oldtimestamp =
    CALCULATE (
        MAX ( 'MyTable'[PositionTimestampp] ),
        MyTable[PositionTimeStampp] < EARLIEST ( MyTable[PositionTimeStampp] ),
        ALLEXCEPT ( 'MyTable', MyTable[Identifiant  ] )
    )
RETURN
    (
        CALCULATE (
            MAX ( MyTable[Long] ),
            FILTER ( MyTable, MyTable[PositionTimeStampp] = oldtimestamp )
        )
    )

     

    The results will be as shown:

     

4 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    Hey Anonymous ,

     

    Please try this code to create new calculated column:

     

    requiredLatValue =
VAR oldtimestamp =
    CALCULATE (
        MAX ( 'MyTable'[PositionTimestampp] ),
        MyTable[PositionTimeStampp] < EARLIEST ( MyTable[PositionTimeStampp] ),
        ALLEXCEPT ( 'MyTable', MyTable[Identifiant  ] )
    )
RETURN
    (
        CALCULATE (
            MAX ( MyTable[Lat] ),
            FILTER ( MyTable, MyTable[PositionTimeStampp] = oldtimestamp )
        )
    )

    requiredLongValue =
VAR oldtimestamp =
    CALCULATE (
        MAX ( 'MyTable'[PositionTimestampp] ),
        MyTable[PositionTimeStampp] < EARLIEST ( MyTable[PositionTimeStampp] ),
        ALLEXCEPT ( 'MyTable', MyTable[Identifiant  ] )
    )
RETURN
    (
        CALCULATE (
            MAX ( MyTable[Long] ),
            FILTER ( MyTable, MyTable[PositionTimeStampp] = oldtimestamp )
        )
    )

     

    The results will be as shown:

     

  • arichard19's avatar
    arichard19
    Resolver I
    4 years ago

    Try re-using your original line as follows ->

    X = CALCULATE( MAX('Table'[Long]),'Table'[PositionTimeStampp] < EARLIEST('Table'[PositionTimeStampp]),ALLEXCEPT('Table','Table'[dentifiant ]))
    This will result in 

     

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could create a couple of measures like

    Old Lat =
VAR currentTimestamp =
    SELECTEDVALUE ( 'TABLEPOSITION'[Position Timestamp] )
RETURN
    CALCULATETABLE (
        SELECTCOLUMNS (
            TOPN ( 1, 'TABLEPOSITION', 'TABLEPOSITION'[Position Timestamp] ),
            "@val", 'TABLEPOSITION'[Lat]
        ),
        'TABLEPOSITION'[Position Timestamp] < currentTimestamp
    )

    and then same again for longitude

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you everyone for your solution, I was able to succeed with the solution of PC2790