cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TrentS Regular Visitor
Regular Visitor

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.

Trent

2 REPLIES 2
Community Support Team
Community Support Team

Re: Count of events per period

Hi @TrentS ,

I create a measure to calculate the cases as open.

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

6.PNG

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 Regular Visitor
Regular Visitor

Re: Count of events per period

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

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)