Forum Discussion

renanc's avatar
renanc
Icon for Helper II rankHelper II
4 years ago
Solved

Calculate the previous within the same information

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's avatar
    tamerj1
    4 years ago

    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 )

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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 )