Forum Discussion

syasmin25's avatar
syasmin25
Icon for Helper V rankHelper V
6 years ago

Days Membership

Hello, 

I am trying to calculate total days a student has attended a school. I have a table (t_Student_Enrollment) that has the Student's Enter and Leave Dates and another table(t_Calendar) that has the School Calendar. There is a column "Days_count" that show "0" for when the School is closed(weekends, holidays, breaks, etc.) and "1" for when the School is open. And, the column "total_days" sums the total count of days (cumulative). I am pretty new to DAX. I did create a relationship between the two tables and am trying to retrieve the total number of days. This can be retrieved either by the total of days_count or retrieving the total_days value. I am trying to use the School_Date as a slicer for my visual to look at total days based on different ranges for calculating ranges thereafter. I created a sample and am posting it below.


However, my calculations are not showing the correct number of days. 

t_Student_Enrollment:

** If the Leave_Date column doesn't have any Leave_Date then it shows current date.


t_Calendar:

In order to calculate days, I created a column in t_Student_Enrollment:
Total number of days should be no greater than 121 at the very max, where as this shows way more.

 

Any assistance would be appreciated.

 

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    A column like this should do it (table/column names will be slightly different at your side)

    CALCULATE(SUM(TableDate[DaysCount]), DATESBETWEEN( TableDate[Date], TableStudent[enter] , TableStudent[leave]  ))

    This will work fine with a disconnected date table.

    Make sure you know what your date slicer is giving you if you use a related date table.  Your active relationship will be on startDate or endDate.  I don't think the TotalDays column will be of much use to you - it's fine if everyone started on the first day (11 Aug) but they don't.