Calculate the previous within the same information

[renanc]

Hi everybody,

I am trying to calculate the previous KM for the same car plate.
I tried a Dax Formula to calculate the previous value, but it actually returns the value for the next Checklist. I tried to modify the operators, and also tried LASTNORBLANK instead of FIRSTNONBLANK, but it didn't give me the expected values..

Can anybody help me with this, please?

Thank you

KM Anterior =

CALCULATE(

FIRSTNONBLANK(fChecklists[Digite a KM],1),

FILTER(

ALL(fChecklists),

(fChecklists[DescPlaca])=EARLIER(fChecklists[DescPlaca])

&& fChecklists[IdChecklist] > EARLIER(fChecklists[IdChecklist])

&& fChecklists[IdQuestionario] = EARLIER([IdQuestionario])

&& fChecklists[DtCadastro] >= EARLIER([DtCadastro])

)

)

[tamerj1 · Accepted solution]

Hi @renanc 

please use

KM Anterior =
VAR PlateNumTable =
    CALCULATETABLE (
        fChecklists,
        ALLEXCEPT ( fChecklists, fChecklists[DescPlaca] )
    )
VAR PreviousTimes =
    FILTER (
        PlateNumTable,
        PlateNumTable[DtCadastro] < EARLIER ( PlateNumTable[DtCadastro] )
    )
VAR PreviousTime =
    MAXX ( PreviousTimes, PlateNumTable[DtCadastro] )
RETURN
    MAXX ( PreviousTimes, PlateNumTable[DtCadastro] = PreviousTime )