Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Count of events per period

Afternoon all,


I have approached this one a couple of different ways here over the past several months and my model continues to evolve. I've read many of the issues here and other places but am still struggling. I know it is some variation of a Calculate(Count measure but can't seem to get it right.

This is a time measure as the 'Line' in a Line and stacked column chart showing open cases during a time period (week/month/quarter).Datecolumns.PNG

Each row/case has the actual dates, custom week start/end, month start/end and custom fiscal quarter, the later being assigned through various time intelligence items. I have it there to remove relationship issues with other data sources. (I have 3 primary data sources with additional Excel reference files on this one!)

I do not have a separate date table (but if required I can move to one...)

The measure should count a case was open (using the first row data) as being open in the week periods between 6/22 thru 8/24 and closed on 8/31. It should count the case as OPEN (Status Field) during the months of June and July and Closed in August.

I request your help in getting this measure created.


Community Support
Community Support

Hi @TrentS ,

I create a measure to calculate the cases as open.

OPEN = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Week_End_Close] <= TODAY()))


Best Regards,

Xue Ding

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

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

Thank you Xue Ding for the response.

Unfortunately, that just gives me how many cases are still open from previous periods, not how many were open within that period.

In the first row of the example you used, I should see the case listed as open in the weeks ending May 4th and May 11th, closing May 18th. Instead, it just counts it as open from May 4th.

If, as another solution that I have modeled, I have a row for each case open for each period it is open, your solution might work but it leads to some serious data duplication and bloat. Time Intelligence features should remove the need for that. I just haven't found out how yet....


Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors