Forum Discussion
Count days between two dates, conditional based on another table
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.
- 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!
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" ) )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
4 Replies
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" ) )Thank you. I'm plowing through some other tasks and will followup on this in a few hours.
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
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!
