Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
joep78
Helper III
Helper III

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:

 

OrderAcceptedDateClosedDate
11-1-202015-1-2020
23-1-202018-1-2020
35-1-2020null
47-1-20202-2-2020
57-1-202031-1-2020
61-2-2020null
73-2-2020null
85-2-202018-2-2020
97-2-202020-2-2020
107-2-202018-3-2020
113-3-2020null
124-3-2020null
135-3-2020null
146-3-202015-3-2020
157-3-202020-3-2020
169-3-2020null
179-3-2020null
1810-3-2020null
1911-3-2020null
2020-3-2020null

 

the outcome should be as follow:

monthamount of new tickets amount of open tickets
152
254
31011

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@joep78 

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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.

1.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
joep78
Helper III
Helper III

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
Super User
Super User

@joep78 

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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