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

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

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

Or 

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

 

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

Hi,

 

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

Thanks,

 

Jo 

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

    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
                )
            )
        )
    )
    • JoSwinnen's avatar
      JoSwinnen
      Icon for Helper I rankHelper I
      4 years ago

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

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

        JoSwinnen 

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