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 repres...
Need Help
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.