The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
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.
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |