DAX Formula

[Anonymous]

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
1	21/07/2022 14:50:00		21/07/2022 14:40:00		0,12928		46,80856		0,13119		46,80699
1	21/07/2022 14:40:00				0,13119		46,80699
2	21/07/2022 13:50:00		21/07/2022 13:40:00		0,09237		47,25609		0,09208		47,25606
2	21/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

[Anonymous]

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

[johnt75]

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

[arichard19]

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 

[PC2790 · Accepted solution]

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: