Forum Discussion

ww1711's avatar
ww1711
Frequent Visitor
7 years ago
Solved

Data Summarization by Month

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!

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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's avatar
      ww1711
      Frequent Visitor
      7 years ago
      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.
    • ww1711's avatar
      ww1711
      Frequent Visitor
      7 years ago
      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!