topic Re: DATEDIFF with filter in DAX Commands and Tips

DATEDIFF with filter

Anonymous — Mon, 19 Aug 2019 11:08:13 GMT

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

Re: DATEDIFF with filter

Anonymous — Mon, 19 Aug 2019 11:34:03 GMT

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.

Re: DATEDIFF with filter

Anonymous — Mon, 19 Aug 2019 12:06:35 GMT

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

Re: DATEDIFF with filter

Anonymous — Thu, 22 Aug 2019 19:38:26 GMT

anyone else who can help me?

Re: DATEDIFF with filter

Anonymous — Wed, 04 Sep 2019 07:13:02 GMT

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

Re: DATEDIFF with filter

Anonymous — Wed, 04 Sep 2019 08:48:34 GMT

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.

Re: DATEDIFF with filter

Anonymous — Thu, 05 Sep 2019 10:57:52 GMT

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.

Re: DATEDIFF with filter

Anonymous — Thu, 05 Sep 2019 16:18:52 GMT

shout out to Anonymous you the man.

thanks alot

Greetings,

Charaf