Count rows with condition

[PatrickLamoste]

Hello,

I am trying to count the number of termination using our pay period number as reference. Below is the datedim with the corresponding PayPeriod Number.

I need to display the count in a card but the catch is it is for the previous PayPeriod. For example we are in period 6 but what I need is the termination for period 5.

Thank you!

Date	PayPeriodNumber	Year
01/02/2019	4	2019
02/02/2019	4	2019
03/02/2019	4	2019
04/02/2019	4	2019
05/02/2019	4	2019
06/02/2019	4	2019
07/02/2019	4	2019
08/02/2019	4	2019
09/02/2019	4	2019
10/02/2019	4	2019
11/02/2019	4	2019
12/02/2019	4	2019
13/02/2019	4	2019
14/02/2019	4	2019
15/02/2019	5	2019
16/02/2019	5	2019
17/02/2019	5	2019
18/02/2019	5	2019
19/02/2019	5	2019
20/02/2019	5	2019
21/02/2019	5	2019
22/02/2019	5	2019
23/02/2019	5	2019
24/02/2019	5	2019
25/02/2019	5	2019
26/02/2019	5	2019
27/02/2019	5	2019
28/02/2019	5	2019
01/03/2019	6	2019
02/03/2019	6	2019
03/03/2019	6	2019
04/03/2019	6	2019
05/03/2019	6	2019
06/03/2019	6	2019
07/03/2019	6	2019
08/03/2019	6	2019
09/03/2019	6	2019
10/03/2019	6	2019
11/03/2019	6	2019
12/03/2019	6	2019
13/03/2019	6	2019
14/03/2019	6	2019

Number	Name	Start Date	End Date
1	A	01/01/2018	31/12/2154
2	B	01/01/2018	31/12/2154
3	C	01/01/2018	24/02/2019
4	D	01/01/2018	21/02/2019
5	E	01/01/2018	31/12/2154
6	F	01/01/2018	21/02/2019
7	G	01/01/2018	24/02/2019
8	H	01/01/2018	31/12/2154
9	I	01/01/2018	31/12/2154
10	J	01/01/2018	31/12/2154

[v-juanli-msft · Accepted solution]

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.

[v-juanli-msft]

Hi @PatrickLamoste 

In case you are in period 6,

Would you like to calculate the count of distinct Name if their Start Date and End Date are in Period 5?

Best Regards

Maggie

[PatrickLamoste]

Oh yeah I missed that part. I need to count it using distinct "Number" it's like an employee ID.