Forum Discussion
Solved
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 ...
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 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.