cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Calculate cumulative counts

I am looking to calculate counts based on a condition during a given date.

I would create a date table

I would add a calculated column in that date table.

That calculated column should look at the given date in the row of the column, and iterate over the entire data table to see how many items were open during that date (Open being opened date >= date in row & date in row < closed date)

Anyway I know I'm close, but I have tried a few attempts and the answer eludes me.  I want the output to look like the report below.

DataReport I would Like

1 ACCEPTED SOLUTION
Resolver II

Another option:

You'll need a date table, you can use something like this (though you'll just really need the date for this): https://community.powerbi.com/t5/Desktop/How-do-i-create-a-date-table/td-p/23896

On your date table add a column with this formula: OpenIssues = Calculate(Countrows(Table1),Filter(Table1, Table1[Date Opened] <= LASTDATE(DateTable[Date]) && Table1[Date Closed] >= FirstDate(DateTable[Date])))

Gives a result as such with your sample data:

9 REPLIES 9
Resolver II

Another option:

You'll need a date table, you can use something like this (though you'll just really need the date for this): https://community.powerbi.com/t5/Desktop/How-do-i-create-a-date-table/td-p/23896

On your date table add a column with this formula: OpenIssues = Calculate(Countrows(Table1),Filter(Table1, Table1[Date Opened] <= LASTDATE(DateTable[Date]) && Table1[Date Closed] >= FirstDate(DateTable[Date])))

Gives a result as such with your sample data:

Regular Visitor

I have been looking for this solution for days!! This is so good. Thank you!!!!!

Here is my adaptation of your formula.  Unfortunately it doesnt seem to work for me.  I get all rows of the new column with the same value.

```OpenIssues =
Calculate(Countrows(Issues),
Filter(Issues, Issues[Date Opened] <= LASTDATE(DateTable[Date])
&& Issues[Date Closed] >= FirstDate(DateTable[Date])
)
)```

Can you link your example pbix file so I can take a look through it?

Resolver II

Hmmm, did you build your OpenIssues column on the Date table or the Issues table?  In this design I built it on the Date table.

I'm new to these forums, how do you upload a file?  I'd be happy to email it to you.  In lieu of that here are some screenshots with the details:

I built my openissues column in my date table.  Not sure if it matters but my date table is a calculated table.

PS I sent you a PM with my email.

Community Champion

@jsquaredz

To make @danrmcallister's column work you need to get rid of the relationship between the tables!

There's even a third way to do this if you are interested...

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

Which way you go it really depends on how much further analysis you need to do!

Read @KHorseman's posts at the above link!

BTW

Here's the Active Count formula explained

http://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-cam...

That was it.  Needed to turn off those relationships.  Thanks for that insight.

Community Champion

You'll need a Calendar Table? Assuming you have one...

Create a Relationship from Date in the Calendar to Date Opened (Active) and then again

from Date in the Calendar to Date Closed (Inactive)

Then create these 4 MEASURES

```Opened = COUNTA ( 'Table'[Date Opened] )

Closed =
CALCULATE (
COUNTA ( 'Table'[Date Closed] ),
USERELATIONSHIP ( CalendarTable[Date], 'Table'[Date Closed] )
)

Balance = [Opened] - [Closed]

Running Total =
CALCULATE (
[Balance],
FILTER (
ALL ( CalendarTable ),
CalendarTable[Date] <= MAX ( CalendarTable[Date] )
)
)```

Here's the Chart and result...

Hope this helps!

Anonymous
Not applicable

This worked well for me!  Thank you so much!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors