topic Re: Average duration between two dates is not calculated in DAX Commands and Tips

Average duration between two dates is not calculated

JoSwinnen — Wed, 24 Aug 2022 09:49:11 GMT

Hi,

I have a dataset with multiple lines for one student. For each year he is stuying at the university a line is created with his registrationdate of that year and his deregistrationdate of that year. I want to calculate how long students are studying on average.

I've tried this one, based on https://community.powerbi.com/t5/Desktop/Calculate-Differance-Between-two-dates-grouping-by-another-field/m-p/2143410#M790663

Study duration=

VAR Startdate = calculate(min('vw_powerBI'[registrationdate]),ALLEXCEPT(vw_powerBI,vw_powerBI[studentnumber]))

VAR Enddate = calculate(max('vw_powerBI'[deregistrationdate]),ALLEXCEPT(vw_powerBI,vw_powerBI[studentnumber]))

RETURN

AVERAGEX(vw_powerBI,DATEDIFF(Startdate, Enddate,DAY))

But it is not giving the appropriate result.

Can anyone help?

Kind regards,

Re: Average duration between two dates is not calculated

amitchandak — Wed, 24 Aug 2022 10:11:25 GMT

JoSwinnen , Try like

Study duration=
VAR Startdate = calculate(min('vw_powerBI'[registrationdate]),ALLEXCEPT(vw_powerBI,vw_powerBI[studentnumber]))
VAR Enddate = calculate(max('vw_powerBI'[deregistrationdate]),ALLEXCEPT(vw_powerBI,vw_powerBI[studentnumber]))
RETURN
AVERAGEX(values(vw_powerBI,vw_powerBI[studentnumber]),calculate(DATEDIFF(Startdate, Enddate,DAY)))

Re: Average duration between two dates is not calculated

JoSwinnen — Wed, 24 Aug 2022 10:23:50 GMT

Thanks for this. Now it gives for each student the correct duration but the average is not correct.

And there is another difficulty that I'm realizing. I only want to calculate the study duration if this student has graduated.

So there are different rows for one student and in the last year there will be 'yes' in the column 'graduated?' but in the previous rows there will be 'no'.

Any advice?

Thanks,

Re: Average duration between two dates is not calculated

tamerj1 — Wed, 24 Aug 2022 10:45:54 GMT

Hi JoSwinnen
Please try the following measure. Also I would really appreciate if you share more details about your visual and what columns are you slicing by in this visual.

Study duration = AVERAGEX ( VALUES ( 'vw_powerBI'[studentnumber] ), CALCULATE ( DATEDIFF ( MIN ( 'vw_powerBI'[registrationdate] ), MAX ( 'vw_powerBI'[deregistrationdate] ), DAY ) ) )

Re: Average duration between two dates is not calculated

JoSwinnen — Wed, 24 Aug 2022 11:25:59 GMT

Thanks. This one gives a better result.

The dataset looks like this:

Studentnumber RegistrationDate DeregistrationDate Graduated?

00001 15/09/2019 30/06/2020 No

00001 15/09/2020 30/06/2021 No

00001 15/09/2021 30/06/2022 Yes

00002 15/09/2020 30/06/2021 No

00002 15/09/2021 30/06/2022 No

00003 ...

So the formula above may only work when there is one row with a yes for that student. Students who tried but did not graduated (only no's) cant be included in the calculation.

Thanks,

Re: Average duration between two dates is not calculated

tamerj1 — Wed, 24 Aug 2022 12:19:15 GMT

JoSwinnen
Please try

Study duration = VAR Graduated = CALCULATETABLE ( VALUES ( 'vw_powerBI'[Graduated?] ), ALLEXCEPT ( 'vw_powerBI', 'vw_powerBI'[Studentnumber] ) ) RETURN AVERAGEX ( VALUES ( 'vw_powerBI'[studentnumber] ), CALCULATE ( IF ( "Yes" IN Graduated, DATEDIFF ( MIN ( 'vw_powerBI'[registrationdate] ), MAX ( 'vw_powerBI'[deregistrationdate] ), DAY ) ) ) )

Study duration = VAR CurrentStudentTable = CALCULATETABLE ( 'vw_powerBI', ALLEXCEPT ( 'vw_powerBI', 'vw_powerBI'[Studentnumber] ) ) VAR FiteredTable = FILTER ( CurrentStudentTable, 'vw_powerBI'[Graduated?] = "Yes" ) RETURN AVERAGEX ( FiteredTable, DATEDIFF ( MIN ( 'vw_powerBI'[registrationdate] ), MAX ( 'vw_powerBI'[deregistrationdate] ), DAY ) )

Re: Average duration between two dates is not calculated

JoSwinnen — Wed, 24 Aug 2022 18:57:21 GMT

Hi,

I've tried both ways, but it all gives the same result. The study duration for each student graduated or not...

Thanks,

Re: Average duration between two dates is not calculated

tamerj1 — Wed, 24 Aug 2022 19:08:15 GMT

JoSwinnen

Please try

Study duration = VAR Graduated = CALCULATETABLE ( VALUES ( 'vw_powerBI'[Graduated?] ), ALLEXCEPT ( 'vw_powerBI', 'vw_powerBI'[Studentnumber] ) ) RETURN IF ( "Yes" IN Graduated, AVERAGEX ( VALUES ( 'vw_powerBI'[studentnumber] ), CALCULATE ( DATEDIFF ( MIN ( 'vw_powerBI'[registrationdate] ), MAX ( 'vw_powerBI'[deregistrationdate] ), DAY ) ) ) )

Re: Average duration between two dates is not calculated

JoSwinnen — Wed, 24 Aug 2022 19:29:28 GMT

Thank you for trying, but it still gives the same result.

Re: Average duration between two dates is not calculated

tamerj1 — Wed, 24 Aug 2022 19:36:34 GMT

JoSwinnen

I need to have a look at the data. Maybe we can connect tomorrow if you wish.

Re: Average duration between two dates is not calculated

tamerj1 — Wed, 24 Aug 2022 19:41:09 GMT

JoSwinnen

Would you please share a screenshot of the code you've been using

Re: Average duration between two dates is not calculated

JoSwinnen — Thu, 25 Aug 2022 08:18:09 GMT

Thank you for your help! I think I'm almost there, but I'm working on another project today. I will get back to it!

Re: Average duration between two dates is not calculated

tamerj1 — Thu, 25 Aug 2022 08:44:58 GMT

actually when I saw your reply I had a 2nd look at the solution and the results.
In your report you are slicing by 'vw_powerBI'[stamnummer] which means that the filter context contains the table of each student and there is no need to CALCULATETABLE - ALLEXCEPT. Therefore, the VALUES ( 'vw_powerBI'[Graduated?] ) should contain all the "Yes" and "No" values for each "stamnummer". The limitation I set is: IF at least one "Yes" is available for the current student then do the calculation otherwise return blank. I see no reason why this shouldn't work.

However this condition need to be applied inside the SUMX as well in order to limit the total average (In the column total cell) to only the students who have "Yes" in their Graduate status.

Study duration = IF ( "Yes" IN VALUES ( 'vw_powerBI'[Graduated?] ), AVERAGEX ( VALUES ( 'vw_powerBI'[stamnummer] ), IF ( "Yes" IN CALCULATETABLE ( VALUES ( 'vw_powerBI'[Graduated?] ) ), DATEDIFF ( CALCULATE ( MIN ( 'vw_powerBI'[registrationdate] ) ), CALCULATE ( MAX ( 'vw_powerBI'[deregistrationdate] ) ), DAY ) ) ) )

JoSwinnen

Re: Average duration between two dates is not calculated

JoSwinnen — Tue, 30 Aug 2022 06:50:55 GMT

My apologies for the late reply but now it works, thanks a lot!!!