Using Countrows based on multiple conditions

[Hyuna_8000]

Hi,

I have a dataset with the following following columns, basically I just wanted to have a calculated column, that populates the number of actions when the Action is "Promotion" or "Secondment" or both for each employee. And for employee with no Separation Date, I want the count of the number of Promotion or Secondment or both when the Effective date is between today and 12 months ago. For employee with a Separation Date, the count should be when the Effective date equals or is greater than 12 months before the Separation Date and is less than the Separation Date.

Please see this dataset, the "No. of Promotion and Secondment last 12 months" is the calculated column I want:

Row	Employee Name	Effective Date	Action	Separation Date	No. of Promotion and Secondment last 12 months
1	Jane Jones	8/02/2025	Hire		0
2	Carrie Bradshaw	1/01/2024	Secondment		2
3	Carrie Bradshaw	6/06/2024	Promotion		2
4	Carrie Bradshaw	26/10/2021	Secondment		2
5	Rebecca Lynn	16/10/2022	Hire		1
6	Rebecca Lynn	14/10/2023	Secondment		1
7	Yao Ming	7/10/2022	Secondment	7/06/2023	1
8	Zoe Meng	7/01/2024	Add Worker		0
9	Hoi Lou	5/10/2021	Hire	2/02/2023	1
10	Hoi Lou	5/11/2021	Secondment	2/02/2023	1
11	Hoi Lou	6/06/2022	Promotion	2/02/2023	1

I did try this Dax code for the calculated column, but it only returns either 1 for row with Promotion or Secondment with the effective date and separation date condition. For example, for Carrie Bradshaw, it only returns 1 for the Row 2 and 3, and 0 for Row 4, but what I want is 2 for Row 2, 3 and 4.

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR EffectiveStartDate = 'Data'[Effective Date]
VAR Last12MonthsStartDate = TODAY() - 365
RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
('Data'[Action] = "Promotion" && 'Employment Action History'[Action] = "Secondment"),
'Data'[Effective Start Date] >= Last12MonthsStartDate
),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
('Data'[Action] = "Promotion" && 'Data'[Action] = "Secondment"),
'Data'[Effective Date] >= SeparationDate - 365,
'Data'[Effective Date] <= SeparationDate
)
)

Thanks in advance, much appreciated!

[bhanu_gautam]

@Hyuna_8000 , Try below DAX mentioned:-

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR EffectiveStartDate = 'Data'[Effective Date]
VAR Last12MonthsStartDate = TODAY() - 365

RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
'Data'[Action] IN {"Promotion", "Secondment"},
'Data'[Effective Date] >= Last12MonthsStartDate
),
CALCULATE(
COUNTROWS('Data'),
'Data'[Employee Name] = CurrentEmployee,
'Data'[Action] IN {"Promotion", "Secondment"},
'Data'[Effective Date] >= SeparationDate - 365,
'Data'[Effective Date] < SeparationDate
)
)

[Hyuna_8000]

Hi @bhanu_gautam 
Thanks for your prompt reply, really appreciated it! 
It still returns 1 for individual row that matches the criteria, instead of summing up or count the total rows that match the criteria. I wonder if I should use Sumx instead, so far no luck yet.

[bhanu_gautam · Accepted solution]

@Hyuna_8000 , Yes you can try using SUMX

No. of Promotion and Secondment last 12 months =
VAR CurrentEmployee = 'Data'[Employee Name]
VAR SeparationDate = 'Data'[Separation Date]
VAR Last12MonthsStartDate = TODAY() - 365

RETURN
IF(
ISBLANK(SeparationDate),
CALCULATE(
SUMX(
FILTER(
'Data',
'Data'[Employee Name] = CurrentEmployee &&
'Data'[Action] IN {"Promotion", "Secondment"} &&
'Data'[Effective Date] >= Last12MonthsStartDate
),
1
)
),
CALCULATE(
SUMX(
FILTER(
'Data',
'Data'[Employee Name] = CurrentEmployee &&
'Data'[Action] IN {"Promotion", "Secondment"} &&
'Data'[Effective Date] >= SeparationDate - 365 &&
'Data'[Effective Date] < SeparationDate
),
1
)
)
)