Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list of projects that go through 5 stages in their lifetime: Requirements (Req), Development (Dev), UAT, Implement, & Warranty.
I have dates associated with each of the stages that represent when each stage is complete.
I'm trying to report on the status of each project at the end of each month based on which stage is/was completed that month. So, if a given project's requirements are completed in January and development completes some time in March, I'd expect the output of my report to tell me that the project's statuses for January, February, and March are "Req Complete", "Req Complete", and "Dev Complete" respectively.
This is what the source data looks like:
This is what the output might look like:
Note, that if multiple stages complete in one month, we're only interested in displaying the most recently completed stage; Project A completed both Requirements AND Development in January, but the resulting report shows only "Dev" as the stage completed in January.
Thanks in advance!
Solved! Go to Solution.
Hi,
You may refer to my solution here - Show Project wise status in a Pivot Table.
Hope this helps.
Hi,
You may refer to my solution here - Show Project wise status in a Pivot Table.
Hope this helps.
You are welcome.
Hmm, in reading this again, perhaps something brute force like:
Measure = VAR __year = YEAR(MAX(Calendar[Date])) VAR __month = MONTH(MAX(Calendar[Date])) VAR __reqYear = YEAR(MAX(Projects[Req_FinishDate])) VAR __reqMonth = MONTH(MAX(Projects[Req_FinishDate])) VAR __devYear = YEAR(MAX(Projects[Dev_FinishDate])) VAR __devMonth = MONTH(MAX(Projects[Dev_FinishDate])) VAR __uatYear = YEAR(MAX(Projects[UAT_FinishDate])) VAR __uatMonth = MONTH(MAX(Projects[UAT_FinishDate])) VAR __impYear = YEAR(MAX(Projects[Implement_FinishDate])) VAR __impMonth = MONTH(MAX(Projects[Implement_FinishDate])) VAR __warYear = YEAR(MAX(Projects[Warranty_FinishDate])) VAR __warMonth = MONTH(MAX(Projects[Warranty_FinishDate])) VAR __state = SWITCH(TRUE(), __year = __warYear && __month = __warMonth,"Warranty" __year = impYear && __month = __impMonth,"Implementation" ... RETURN __state
Something along those lines. Also, wondering if unpivoting your date columns might provide a better solution.
I don't think they are a perfect fit for this, but take a look at these two Quick Measures as I think you want something like them or at least they might give you an idea or two:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |