Forum Discussion
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.
- Anonymous6 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
- tex628Community 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- AnonymousNot 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-xicaiCommunity 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.
- AnonymousNot applicable
- AnonymousNot 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)