Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jsquaredz
Advocate I
Advocate I

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.

 

DataDataReport I would LikeReport I would Like

1 ACCEPTED SOLUTION
danrmcallister
Resolver II
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:

 

PBI Counts with Date Ranges.jpg

 

View solution in original post

9 REPLIES 9
danrmcallister
Resolver II
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:

 

PBI Counts with Date Ranges.jpg

 

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?

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:

 

PBI Issue Table.jpgPBI Date Table.jpgPBI Excel.jpg

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.

Sean
Community Champion
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! Smiley Happy

 

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.

Sean
Community Champion
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...

RT - Open and Closed.png

 

Hope this helps! Smiley Happy

Anonymous
Not applicable

This worked well for me!  Thank you so much!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors