Teacher Enrollment counts over time report

[RyanLMoran]

Hello:

     I'm hoping that somebody can fill in a couple of gaps for me and get me moving in the right direction.  We have a report that was created in-house that we want to have recreated in Power BI.  Essentially, this report is a snapshot of a teacher enrollment count over time that is a snapshot into the future.  We want to be able to see that teacher A has 100 enrollments today but some of their enrollments will be completed and the date two weeks in the future will show enrollments as (for example) 80.  This allows administrators to have  projection of workload for a teacher to see what their current and future enrollment workload is to be to allow them to determine if the teacher will be able to take on additional enrolllments.  From what I understand we will need to graph the end dates, not the start dates, in order to get this projection.  I'm a bit confused as to how to start this computation structure to get the enrollment count tied to the teacher at X points of time, over time, over the course of an amount of time.  

Here is a snapshot of the current report structure that we have and that we want recreated in Power BI:

Here are the tables and fields that we have for this that I have loaded in to Power BI:

I believe that I'm going to need to implement a slicer in order to get the drop down to select a specific teacher to display the data for.  I figured that I could worry about that after I got the structure down to get the enrollment counts over time tied to a teach and get the line graph visual put in place.  Unless that should be planned while putting together the query.  Any and all help is appreciated.  Thank you.

[amitchandak]

@RyanLMoran ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[RyanLMoran]

Hi,

   I'm happy to provide any and all that I can to help you help me.  Is this what you are looking for?

[v-shex-msft · Accepted solution]

Hi @RyanLMoran,

You can create a calendar table and use its date field as the axis of the line chart. Then you can write measure expressions to calculate the count of teachers based on current date and teacher table 'end date'.

Measure =
VAR currDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNT ( Table[Teacher] ),
        FILTER ( ALLSELECTED ( Table ), [End Date] >= currDate )
    )

Regards,

Xiaoxin Sheng