Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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:
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:
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:
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.
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...
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
That was it. Needed to turn off those relationships. Thanks for that insight.
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!
This worked well for me! Thank you so much!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
97 | |
38 | |
37 |
User | Count |
---|---|
152 | |
125 | |
75 | |
74 | |
63 |