Count open orders per month

[joep78]

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

[Anonymous]

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.

[joep78]

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!

[amitchandak · Accepted solution]

@joep78 

I have blog on similar topics : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...