datediff problems

[Anonymous]

Good morning,

I wish to return a time (duration) in seconds from a certain element.

As soon as I get the label "Ordre fermeture vanne de garde" which is a closing order for a valve, i want to get the duration till i reach "Vanne de garde fermée" which indicates, thanks to a sensor, that the valve is closed. And i have to pay attention to which valve orders is, for this i got a column name "EGF".

This is how some dummies data looks like. I start a duration from a "Présente" (present) statement closing order, and finish to the first "Présente" statement closed valve. As i said before, i only return a time from same EGF.

Here you can download some datas : download link 

(password is : datediff)

I've tried some method, the more close one is this one :

Durée(secondes) = IF('test_vdg'[Description de l'évenement] = "Vanne de garde fermée";
        DATEDIFF(
            CALCULATE(
                MAX('test_vdg'[Date et heure]); 
                FILTER(
                    ALLEXCEPT(
                        'test_vdg'; 
                        'test_vdg'[EGF]); 
                        'test_vdg'[Description de l'évenement] < EARLIER('test_vdg'[Description de l'évenement]))); 
            'test_vdg'[Description de l'évenement]; SECOND); 0)

But it only return the duration from the previous label and it's not what i'm looking for.

If you need few more explainations, i'm here.

[v-xicai]

Hi @Anonymous ,

You may create measure like DAX below.

Durée(secondes) =

Var _FirstDate= CALCULATE(MIN('test_vdg'[Date et heure]),FILTER(ALLSELECTED('test_vdg'), 'test_vdg'[EGF] =MAX('test_vdg'[EGF])&& 'test_vdg'[Date et heure]< MAX('test_vdg'[Date et heure])))

Var _LastDate= CALCULATE(MAX('test_vdg'[Date et heure]),FILTER(ALLSELECTED('test_vdg'), 'test_vdg'[EGF] =MAX('test_vdg'[EGF])&& 'test_vdg'[Date et heure]<= MAX('test_vdg'[Date et heure])&&'test_vdg'[Description de l'évenement] = "Vanne de garde fermée"))

Return

IF('test_vdg'[Description de l'évenement] = "Vanne de garde fermée", DATEDIFF( _FirstDate ,_LastDate, SECOND), 0)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xicai

the dax formula only return one result for every "Vanne de garde fermée" which is 31915350.

[Anonymous · Accepted solution]

I figured it out,
thank you @v-xicai for the referral by using variables.

For those of you who might be interested, here's the final formula :

Durée fermeture(secondes) = 

Var _DebutDate = 
    CALCULATE(
        MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
        FILTER(
            ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]), 
            GU_BasseMaulde_CDEVDG[Date et heure]
            < EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Ordre fermeture vanne de garde" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))

Var _FinDate = 
    CALCULATE(
        MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
        FILTER(
            ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]), 
            GU_BasseMaulde_CDEVDG[Date et heure]
            <= EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))

RETURN
    IF('GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && 'GU_BasseMaulde_CDEVDG'[Etat de l'évenement] = "Présente", DATEDIFF(_DebutDate , _FinDate, SECOND), 0)

[tex628]

Am i correct in thinking that for "G01" you're looking for the difference in time for these two rows:

Which should be something along the line of 2 hours and 34 min ?

/J

[Anonymous]

No your not,

like i put on the 2nd picture, i need to get the time between the "start point" to the "finish point"