Cumulative Count with KPI

Question

Hi, cumulative counts are covered extensively here but I can't see an answer to my specific problem.

I'm trying to cumulatively count the number of calls a team of agents make in a day and benchmark them against a cumulative KPI.

First I summarize the the number of agents per day based on the calls table.

Active Agents = CALCULATE(COUNTROWS(SUMMARIZE(Calls,Calls[Date],Calls[AgentID])),FILTER(Calls,Calls[Role]="SalesAgent"))

Then create the cumulative KPI for the calls up to yesterday and summed over "This Week"

Calls KPI = CALCULATE([Active Agents]*60,ALL(Dates),Dates[This Week]="This Week",Dates[Date]<TODAY())

This works fine but when I do something similar for the number of calls they actually make during the week the KPI only shows me the previous day's total.

Outbound Calls = CALCULATE(COUNTROWS(Calls),FILTER(Calls,Calls[Type]="O"),FILTER(ALLSELECTED(Dates),Dates[Date]<TODAY()))

You can see in the images above when I inclide a date on the trend axis for the KPI the calls KPI is cumulative however the actual count of calls made is not. Removing the date gives the true cumulative figure for calls made but removes the trend axis.

Can someone please help?

stevedep · Answer

Hi,You have ALLSELECTED in your formula, this might be restricting your data.

tjpcrabfat · Answer

Hi,&nbsp;Thanks for the response. I've tried a few different filters within the CALCULATE function and nothing seems to work. I've tried ALL and also tried date &lt; MAX(date). Can't understand why it works for the "Calls KPI" measure but not for the actual count of the calls.&nbsp;Probably should have mentioned I'm struggling to get my head around context!&nbsp;Cheers,&nbsp;

stevedep · Answer

If you share you data model, some detailed sample data, it can easily be fixed.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Forum Discussion

Cumulative Count with KPI

3 Replies

Related content

Cumulative Count DAX

Cumulative count of ticket

Cumulative Total

Counting nulls

Cumulative Count by Date