Forum Discussion
JoSwinnen
Helper I
4 years agoAverage duration between two dates is not calculated
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...
- 4 years ago
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 ) ) ) )