Forum Discussion

JoSwinnen's avatar
JoSwinnen
Icon for Helper I rankHelper I
4 years ago
Solved

Average 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 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,
 
Jo 
Need Help
  • tamerj1's avatar
    tamerj1
    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
            )
        )
    )
)

     

    JoSwinnen

13 Replies

  • JoSwinnen's avatar
    JoSwinnen
    Icon for Helper I rankHelper I
    4 years ago

    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!

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      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
            )
        )
    )
)

       

      JoSwinnen

      • JoSwinnen's avatar
        JoSwinnen
        Icon for Helper I rankHelper I
        4 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

    • JoSwinnen's avatar
      JoSwinnen
      Icon for Helper I rankHelper I
      4 years ago

      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,

       

      Jo 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

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