Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

datediff problems

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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

5 Replies

  • tex628's avatar
    tex628
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      No your not,

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

  • v-xicai's avatar
    v-xicai
    Community Support

    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's avatar
      Anonymous
      Not applicable

      v-xicai

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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)