Dynamic Count If Measure

[cake28424]

Hi! I have been trying to create a dynamic count if measure but am struggling a lot to do this. Essentially, I want to count the number of active IDs (using dummy data to not share confidential data) within a department at a given time.

When I filter to a particular time period, say 2024, I want a measure to return the number of IDs within a department. So Marketing would return 1, Finance would return 1, HR would return 1, and Sales would return 2. This measure needs to be on a department basis so each ID row returns the active count of all active IDs within a department. I need to use a measure instead of a column so this metric works dynamically with the date filter I have set up (used a date table that accounts for contract start and end year). 

Can someone help me figure out how to do this? Happy to provide any clarification.

[v-jiewu-msft · Accepted solution]

Hi @cake28424 ,

First of all, many thanks to @lbendlin  for your very quick and effective replies, and I will give some additions below:

1.Create the simple table.

2.Create the new measure to count.

Dynamic_Count_ID = 
VAR yea = 2024
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Start year] <= yea &&
        'Table'[End year] >= yea
    )
)

3.Drag the department field and measure into the table visual.

4.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

[cake28424]

Hi @lbendlin  

Thanks for taking a look! I opened up your solution and saw that the customer IDs under the sales department are returning the individual count instead of the department count when the year is 2024. Would want both of these numbers to show 2 instead of 1 each to reflect the department size at the time of 2024.

Do you know if this is possible/how I can achieve this?