Count days between two dates, conditional based on another table

[ambere]

Hi,

I’ve been searching for a solution and I find things that are close but still can’t figure out how to make it work for me. I need a simple formula to count school days of enrollment. I have two tables. 

1. Enrollment - 17000ish rows like this 

Student ID	Entry date	Exit date
12345678	8/22/2022	6/10/2023

Note: exit date is automatically set for the last day of school, but days of enrollment needs to be through today, not through the future date. 

2. Base calendar with a format like this, for the whole year

Date	School day
8/22/2022	School day
8/23/2022	School day
8/24/2022	Holiday
8/25/2022	Weekend

I need to calculate school days enrolled by using the start and end date in table 1 (end date should be either end date or today, whichever is sooner.) while only counting School Days in my base calendar.

I'm attaching two sample files that should help. I'm not sure how to describe the relationship between the files. They are linked by date. Both the Entry Date and Exit Date from table 1 should linked many-to-one with the Base Calendar in Table 2. Thanks so much for your assistance. I love to figure things out myself but help with this would really accelerate my work!

[johnt75 · Accepted solution]

Try

Num school days = SUMX( Student,
    var StartDate = Student[Current Entry Date]
    var EndDate = MIN( TODAY(), Student[Current Exit Date] )
    return CALCULATE(
        COUNTROWS( 'Date' ),
        'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate,
        'Date'[Type] = "School day"
    )
)

[v-yueyunzh-msft · Accepted solution]

Hi , @ambere 

According to your description , you want to calculate school days enrolled by using the start and end date in table 1 (end date should be either end date or today, whichever is sooner.) 

Here are the steps you can follow:

(1)My test data is the same you provided.

(2)We can create a calculated column :

Days = 
var _exitdate=IF([Current Exit Date]>TODAY(),TODAY(),[Current Exit Date])
return
COUNTROWS(FILTER(Sheet1,'Sheet1'[Date]>=[Current Entry Date]&&'Sheet1'[Date]<=_exitdate&&'Sheet1'[Type]="School Day"))

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ambere]

Thank you! This works, in addition to the other solution posted.

Sorry for my delayed reply. I couldn't figure out how to log back in here. Yikes!

[johnt75 · Accepted solution]

Try

Num school days = SUMX( Student,
    var StartDate = Student[Current Entry Date]
    var EndDate = MIN( TODAY(), Student[Current Exit Date] )
    return CALCULATE(
        COUNTROWS( 'Date' ),
        'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate,
        'Date'[Type] = "School day"
    )
)

[ambere]

Thank you. I'm plowing through some other tasks and will followup on this in a few hours.