Forum Discussion
Average duration between two dates is not calculated
- 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 ) ) ) )
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!
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
)
)
)
)
- JoSwinnen4 years ago
Helper I
My apologies for the late reply but now it works, thanks a lot!!!