DATEDIFF with filter

[cbouichi]

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	B	TG	6-2-2019
200	TG	B	7-2-2019
200	B	A	8-2-2019

I need to now when a job has a new status B (busy) how long did this take?

so for example joborder 115728 got the status B at 2-3-2018, therefore it has status TG 18-1-2018.

the outcome must be the datedifferents between 2-3-2018-18-1-2018 in days.

can somebody help me with a measure or a calculated column if a measure is not possible?

Kind regards,

Charaf

[Anonymous · Accepted solution]

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.

[cbouichi]

shout out to @Anonymous  you the man.

thanks alot

Greetings,

Charaf

[cbouichi]

anyone else who can help me?

[Anonymous]

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
)
)

[cbouichi]

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

 

[Anonymous]

The formulation of the conditions is ambiguous. Please give a full explanation of what you want to calculate for Job 200 as it looks like a job can go through stage B several times.

Best
D.

[cbouichi]

Job 200 = new status "B" = old status TG

TG had new status date 6-2-2019 -  B had the new status date 7-2-2019.

so the new status B from the old status TG is one day old.

I hope this is better. @Anonymous