Days Enrolled to Calculate Attendance

[syasmin25]

Hello, 

I am trying to calculate attendance for a School. I am still very new to DAX and needed some help. I am calculating Days Enrolled for Students to Calculate Attendance. I have a table (tStudent_Enrollment) that has the Student ID and their Enter and Leave Dates. And I have another table that has the Calendar Days. I have a Binary Column(Days-Count) where "1" means School Day and "0" means Holiday. I have tried to create relationship and calculate the days enrolled but I am unable to create two relationships to connect the School Date with that of the Enter and Leave Date. Please help me out! I am attaching images of the two tables here. 
Thank you for your time and consideration.
tStudent_Enrollment:

tSchedule:

Update 02.26.2020 

I tried to get the days enrolled from the two tables that I had provided above in picures. One is a date table that has 0 for Holidays and 1 for School days in Days-Count column. The holidays are not only Weenkends but also Winter Break,SpringBreak,National Holiday, etc. 
The formula that I have used to calculate Days Enrolled is:

Days =

Var TBL_Date = CALENDAR('tStudent_Enrollment'[Enter_Date],'tStudent_Enrollment'[Leave_Date])

Var TBL_FinalDate =

ADDCOLUMNS(TBL_Date,

"School_Days",IF(WEEKDAY([Date],2)>= 6,0,1),

"Off_Days",IFERROR(LookupValue('tStudent_Enrollment'[DAY_COUNT],'tStudent_Enrollment'[School_Date],[Date]),0)

)

Return

SUMX(TBL_FinalDate,IF([School_Days]=1 && [Off_Days]=0, 1,0))

However, the days enrolled shows 140 as of yesterday for most whereas, it is supposed to be under 120.

[vivran22 · Accepted solution]

That's great @syasmin25!

For simplicity, you can have a calculated column for handling blank date values:

Leave_Date2 = IF(ISBLANK([Leave_Date]),TODAY(),[Leave_Date])

and you may use it in place of orginal Leave_Date

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[vivran22]

Hello @syasmin25,

Are you trying to link Enter_Date & Leave_Date with School_Date?

Also, what is the output you are looking for in terms of attendance calculation?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[syasmin25]

I was trying to calculate the "Days Enrolled" which I have figured out. However, due to null values in my Leave Date, my calculation is having errors. Could you please tell me what would be a good way to replace the blanks with todays date. Thank you!

[vivran22 · Accepted solution]

That's great @syasmin25!

For simplicity, you can have a calculated column for handling blank date values:

Leave_Date2 = IF(ISBLANK([Leave_Date]),TODAY(),[Leave_Date])

and you may use it in place of orginal Leave_Date

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[syasmin25]

I had another question, so I used Calendar formula to calculate the days enrolled. However, it is now stating that "the start date in calendar function can’t be later than the end date". I do need it to work that way in order to calculate the days enrolled. Is there any solution to this?

[v-frfei-msft]

Hi @syasmin25 ,

We canuser a meassure as below without creating relationship between your tables.

days = 
VAR ent =
    MAX ( 'Table'[Enter_Date] )
VAR le =
    MAX ( 'Table'[Leave_date] )
VAR w1 =
    CALCULATE (
        COUNTROWS ( 'date' ),
        FILTER (
            'date',
            'date'[Date] >= ent
                && 'date'[Date] <= le
                && 'date'[count] = 1
        )
    )
VAR wnon =
    CALCULATE (
        COUNTROWS ( 'date' ),
        FILTER (
            'date',
            'date'[Date] >= ent
                && 'date'[Date] <= TODAY ()
                && 'date'[count] = 1
        )
    )
RETURN
    IF ( ISBLANK ( le ), wnon, w1 )

For more details, please check the pbix as attached.

[syasmin25]

Hey, 

Thank you for your time. It seems correct and normally I would have done it that way. Its just that there are holidays on weekdays in School (National Holiday, Winter Break, etc.) so that would not be possible with this way.

[vivran22 · Accepted solution]

Is it possible for you to share the table and measures you have created? It would help in providing specific solution?

Or if you can share the pbix file with sample data?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[syasmin25]

I tried to get the days enrolled from the two tables that I had provided above in picures. One is a date table that has 0 for Holidays and 1 for School days in Days-Count column. The holidays are not only Weenkends but also Winter Break,SpringBreak,National Holiday, etc. 
The formula that I have used to calculate Days Enrolled is:

Days =

Var TBL_Date = CALENDAR('tStudent_Enrollment'[Enter_Date],'tStudent_Enrollment'[Leave_Date])

Var TBL_FinalDate =

ADDCOLUMNS(TBL_Date,

"School_Days",IF(WEEKDAY([Date],2)>= 6,0,1),

"Off_Days",IFERROR(LookupValue('tStudent_Enrollment'[DAY_COUNT],'tStudent_Enrollment'[School_Date],[Date]),0)

)

Return

SUMX(TBL_FinalDate,IF([School_Days]=1 && [Off_Days]=0, 1,0))

However, the days enrolled shows 140 as of yesterday for most whereas, it is supposed to be under 120.