Hi everyone,
I'm very new to Power BI and wondered if anyone can help translating an Excel file into Power BI. I've got two tables, I'll call them Students and Calendar. I need to show how many students are placed in a particular team on each day of the month.
Student
| Student_name | Team_name | Start_date | End_date |
| Student A | Team 1 | 04/08/2021 | 10/08/2021 |
| Student B | Team 1 | 07/08/2021 | 09/08/2021 |
| Student C | Team 2 | 06/08/2021 | 11/08/2021 |
Now I need to create a Power BI report that looks like below, each row is a placement and in the calendar section each number is a count of students in placement on that day.
| | 05/08/2021 | 06/08/2021 | 07/08/2021 | 08/08/2021 | 09/08/2021 | 10/08/2021 | 11/08/2021 | etc |
| Team 1 | 1 | 1 | 2 | 2 | 2 | 1 | 0 | |
| Team 2 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | |
The calendar table has a column called Date:
Calendar = CALENDAR( DATE(2021,1,1), TODAY())
Now I'm trying to add a new column to the table that adds the number for each day but it doesn't seem to work:
StudentsPerDay= COUNTROWS(FILTER('Student', 'Student'[Start_date] >= 'Calendar'[Date] && 'Student'[End_date] <='Calendar'[Date]
The formula doesn't give any errors though no values are added where there should be values.
Any ideas?
Many many thanks!
