March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
I have blog on similar topics : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
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.
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!
I have blog on similar topics : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |