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  ...
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:

     

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