Count of events per period

[TrentS]

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).

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.

Trent

[v-xuding-msft]

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.

[TrentS]

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....