Calendar with daily counter of students placed

[cipriannsft]

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!

[amitchandak]

@cipriannsft , refer this blog or the attached file if that an help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

The file is not on your data, attached after signature