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...
Need Help
  • 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)
v-xicai's avatar
v-xicai
Community Support
6 years ago

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
6 years ago

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