Data Summarization by Month

[ww1711]

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!

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here - Show Project wise status in a Pivot Table.

Hope this helps.

[ww1711]

Thank you for your help, Ashish. Using your Power Query steps, I was able to transform my data and shape it according to the requirement.

Thanks again!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

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.

[ww1711]

Thanks, Greg. This solution almost got me over the hurdle. Thanks for the time. I'll be saving the links in your prior answer for future reference.

[Greg_Deckler]

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