Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DATEDIFF with filter

Hallo everybody,   I have a table that looks something like this, Job old status new status old date 115728 o TG 18-1-2018 115728 TG B 2-3-2018 115728 B A 2-3-2018 200 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    This code does exactly what you need it to do.

     

    Previous Date =
VAR PreviousRow =
    TOPN (
        1;
        FILTER (
            Blad1;
            Blad1[old date] < EARLIER ( Blad1[CRH_REQDATE] )
                && Blad1[job] = EARLIER ( Blad1[job] )
        );
        Blad1[old date]; DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow; Blad1[old date] )
RETURN
    DATEDIFF ( PreviousValue; Blad1[old date]; DAY )

     

    If this reply solved your issues be sure to mark it as the solution.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

End_date =
IF (
Test[new status] = "B",
DATEDIFF (
CALCULATE (
MAX ( Test[old date] ),
FILTER ( Test, Test[old date] < EARLIER ( Test[old date] ) )
),
Test[old date],
DAY
)
)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous  thank you for your help.

 

im feeling im getting close.

The picture shows the results that i get. the day counts is not the correct outcoming.

I dont understand where this 4 comes from?  the difference between 30 april 2018 - 1 may 2018 = not 4 days.

can you help me with that?

 

i think CHR_OLDVALUE  and CRH_EVENT is missing in the formula.