Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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!
@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
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |