# of projects, per month/year/assignee, via status based on multiple milestone dates

[byrnes]

Good morning Power BI users and forum! New here, read through the best practices guide, and will try to do my best in order to get the best help I can get!

Our project work flow is as such (for assignees) - essentially every time a project moves into a new status, a new date/time stamp is created:
- Status: Ready for Work, Date/Time Stamp: Date Assigned (Production)

- Status: In Progress, Date/Time Stamp: Date Started (Production)

- Status: Ready for Work (QA), Date/Time Stamp: Date Assigned (QA) [and/or] Date Completed (Production)

- Status: In Progress (QA), Date/Time Stamp: Date Started (QA)

- Status: Awaiting Approval, Date/Time Stamp: Date Assigned (BM) [and/or] Date Completed (QA)

- Status: Approval in Progress, Date/Time Stamp: Date Started (BM)
- Status: Closed, Date/Time Stamp: Date Completed (BM)

Goal:
Make a clustered column chart visual that shows how many projects (using distinct of Ticket ID) are in each status per month, during that month (ie if a project was in 'In Progress' during June, it needs to be categorized as such - aka, has a Date Started (Production) date but not a Date Completed (Production) Date in June). This is easy to deduce logically, but I cannot convert it into a measure like I have for much simpler tasks.

What I've tried:

Status can be reverse engineered by hand based on which dates have values and then sorting them into which month they were in during each status. I can get a single status to work, for example checking how many projects were closed every month for a 12 month block. But then once I try to drop more than one of these measures into a visual, since they rely on two different date columns as conditions, the visual seemingly becomes over complex if not outright breaks.

Approved Projects per Month per Assignee = CALCULATE(COUNT('Projects'[Ticket ID]),ALLEXCEPT('Projects','Projects'[Date Completed (BM)].[Year],'Projects'[Date Completed (BM)].[Month],'Projects'[Assignee]))

I have attached a link to the .pbix file on google:
https://drive.google.com/file/d/1FPQV-GNYYZSsFk8wAVq-_PgDALtJd3ug/view?usp=drive_link

[foodd]

To help the forum members further analyze your current state and visualize your issue, could you please provide:

• Your work-in-progress PBIX file, using sanitized data if necessary
• The following YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• For a  reader who is unfamiliar with your business or use case, please add any additional details which may help add meaningful logic or a solution.