topic Re: Calculating days in school in DAX Commands and Tips

Calculating days in school

michelleburke1 — Thu, 09 Jul 2020 18:08:02 GMT

I have a file of days that were instructional days in school and a file that shows what days students were enrolled and what date they left the district. I am trying to calculate how many instructional days a student was enrolled. Is there a DAX command that would help me accomplish this?

Re: Calculating days in school

Greg_Deckler — Thu, 09 Jul 2020 18:38:05 GMT

michelleburke1 - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Calculating days in school

michelleburke1 — Thu, 09 Jul 2020 18:57:22 GMT

Thank you. I have two files - one that contains a date and the Day type

School Date Day Type

123School 9/4/2019 Instructional Day

123School 9/5/2019 Instructional Day

I have another file that has student information including the day they started school. Some start at the beginning of the year but many come later in the school year. So I need to calculate how many days the student has been in school based on todays date minus their start date but only counting days that are in the instructional day file.

I then use this to calculate a percentage of absent days.

Re: Calculating days in school

Greg_Deckler — Fri, 10 Jul 2020 05:37:06 GMT

michelleburke1 - Well, it ought to be something along the lines of this then:

Measure = COUNTROWS( FILTER( 'InstructionalDaysTable', [Date] >= MAX('StudentsTable'[StartDate]) && [Date] <= TODAY() ) )

There are lots of assumptions in here because you really haven't provided the information required to provide you with a specific solution but basically, you are figuring out the start date of the student and then just counting the rows in your "instructional days" table that fall between that date and today. You would put that measure in a table along with your student names or ID's or what not.

Again, difficult to be specific with the information provided.

Re: Calculating days in school

sturlaws — Wed, 05 Aug 2020 20:04:18 GMT

Hi michelleburke1

are you still having issues with this, or is it solved?

Cheers,
Sturla