Forum Discussion
Count Between Two Dates
Hi, I have reviewed other solutions but none seem to do what i want it to do. I have a list of agents in a DimParty Table which has a start date column and an expected end date column. I have a D...
Try adding in an ALL and I recommend modifying your FILTER too. Replace Date with Dates and Agents with DimParty.
AgentCount =
SUMX(
'Date',
CALCULATE(
COUNT( Agents[AgentID] ),
ALL( Agents ),
FILTER(
ALL( Agents[StartDate], Agents[EndDate] ),
Agents[StartDate] <= 'Date'[Date]
&& Agents[EndDate] >= 'Date'[Date]
)
)
)
Pat
Thanks Pat, I've managed to pick apart on your help and get to where i need to be 🙂
Just to note, I didnt help the situation by not referring to agent table as DimParty, so i have amended the text above (to clarify, there is no DimAgent table, its DimParty which holds the agent details, i just tripped over myself)