Forum Discussion
Count open orders per month
Hi all,
I'm looking for a solution for the follwoing user story:
We want to count all open issues per each month whereby the ClosedDate is null or not in the same month.
I formatted the table in an Excel example:
| Order | AcceptedDate | ClosedDate |
| 1 | 1-1-2020 | 15-1-2020 |
| 2 | 3-1-2020 | 18-1-2020 |
| 3 | 5-1-2020 | null |
| 4 | 7-1-2020 | 2-2-2020 |
| 5 | 7-1-2020 | 31-1-2020 |
| 6 | 1-2-2020 | null |
| 7 | 3-2-2020 | null |
| 8 | 5-2-2020 | 18-2-2020 |
| 9 | 7-2-2020 | 20-2-2020 |
| 10 | 7-2-2020 | 18-3-2020 |
| 11 | 3-3-2020 | null |
| 12 | 4-3-2020 | null |
| 13 | 5-3-2020 | null |
| 14 | 6-3-2020 | 15-3-2020 |
| 15 | 7-3-2020 | 20-3-2020 |
| 16 | 9-3-2020 | null |
| 17 | 9-3-2020 | null |
| 18 | 10-3-2020 | null |
| 19 | 11-3-2020 | null |
| 20 | 20-3-2020 | null |
the outcome should be as follow:
| month | amount of new tickets | amount of open tickets |
| 1 | 5 | 2 |
| 2 | 5 | 4 |
| 3 | 10 | 11 |
Let's take Month 1 as an example, there are 5 new tickets but 2 of them were still open at the end of January since the Closed date is either null or in the next month. same for february and march ofcourse.
What options do I have to create this kind of outcome, I hope somebody has an idea and can help me out with this!
Thanks in advance for feedback!
Greetz Joep
I have blog on similar topics : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
3 Replies
I have blog on similar topics : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Hi amitchandak ,
Thanks for your quick reply, I will dive into your blog and your example file but after a quick scan, this solution fits my user story. I will let you now via the SOLUTION as soon as I'm ready!
Hi joep78 ,
Please check the following setps.
1# Create a CALENDAR table and a calculated column "Month".
CALENDAR = CALENDAR(MIN('Table'[AcceptedDate]),MAX('Table'[ClosedDate])) month = MONTH('CALENDAR'[Date])2# Create measures as below.
amount of new tickets = CALCULATE(COUNT('Table'[Order]),FILTER(ALLSELECTED('Table'),'Table'[AcceptedDate].[MonthNo]=SELECTEDVALUE('CALENDAR'[month]))) amount of open tickets = CALCULATE(COUNT('Table'[Order]),FILTER(ALLSELECTED('Table'),'Table'[AcceptedDate].[MonthNo]<=SELECTEDVALUE('CALENDAR'[month])&&('Table'[ClosedDate]=BLANK()||'Table'[ClosedDate].[MonthNo]>SELECTEDVALUE('CALENDAR'[month]))))3# Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
