cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## 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.

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!

2 ACCEPTED SOLUTIONS
Super User

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"
)
)
Community Support

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 4
Community Support

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

Regular Visitor

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!

Super User

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"
)
)
Regular Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors