Count rows with condition

Hi PatrickLamoste 

Tested with your two example table,

1. I name first table as "PayPeriod table", second as "employee table",

then i create a new table in Power BI, 

slicer period = SUMMARIZE('PayPeriod table','PayPeriod table'[Year],'PayPeriod table'[PayPeriodNumber])

These three tables have no relationships.

2. Add "year" and "period" column from 'slicer period' table in the slicer,

3. create measures in "PayPeriod table"

pre selected period = 
IF(MAX('PayPeriod table'[PayPeriodNumber])=SELECTEDVALUE('slicer period'[PayPeriodNumber])-1,1,0)

max = 
CALCULATE(MAX('PayPeriod table'[Date]),FILTER(ALL('PayPeriod table'),[pre selected period]=1))

min = 
CALCULATE(MIN('PayPeriod table'[Date]),FILTER(ALL('PayPeriod table'),[pre selected period]=1))

4. Create measures in "employee table",

condition = 
IF([min]>=MAX('employee table'[Start Date])&&[max]<=MAX('employee table'[End Date])

count = 
CALCULATE(DISTINCTCOUNT('employee table'[Number]),FILTER(ALL('employee table'),[condition]=1))

Please downloadmy pbix for more details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.