Forum Discussion
Distribute date ranges into groups automatically
Hello community folks!
I am trying to break project date ranges into 3 equal groups (where possible) I have a list of all dates each project was worked, and want to automatically show which 3rd a date falls into. See below
| Project 1 | 7/4/2019 | 1 |
| Project 1 | 7/5/2019 | 1 |
| Project 1 | 7/6/2019 | 2 |
| Project 1 | 7/7/2019 | 2 |
| Project 1 | 7/8/2019 | 3 |
| Project 1 | 7/9/2019 | 3 |
| Project 2 | 8/10/2019 | 1 |
| Project 2 | 8/11/2019 | 2 |
| Project 3 | 5/4/2019 | 1 |
| Project 3 | 5/5/2019 | 2 |
| Project 3 | 5/6/2019 | 3 |
I have looked up Rank functions, etc, but am finding nothing where i can set the max number at 3. Can anyone help me with this?
Hi Anonymous ，
First create a column as below:
Rank = RANKX(FILTER('Table','Table'[Project #]=EARLIER('Table'[Project #])),'Table'[Dates worked],,ASC)Then create a measure as below:
Thirds = var _max=MAXX(FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #])),'Table'[Rank]) var _count=CALCULATE(COUNT('Table'[Project #]),FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #]))) Return IF(_max<=3,MAX('Table'[Rank]),DIVIDE(MAX('Table'[Rank]),_count/3))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
11 Replies
Anonymous Sorry, not following this completely. Are you showing us sample source data, expected output, both? If this is sample source data can you post expected output and the logic behind how you want the transformation to happen?
Are you simply looking for the MAX number per project in the last column? You could do that via Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
In your case,
Measure = VAR __Project = MAX([Project]) VAR __MaxStatus = MAXX(FILTER('Table',[Project]=__Project),[Status]) RETURN __MaxStatusSorry about that Greg_Deckler I showed the final output i was going for.
I have a list of projects and all the dates those projects were worked on. shown here.
Project # Dates worked Project 1 7/4/2019 Project 1 7/5/2019 Project 1 7/6/2019 Project 1 7/7/2019 Project 1 7/8/2019 Project 1 7/9/2019 Project 2 8/10/2019 Project 2 8/11/2019 Project 3 5/4/2019 Project 3 5/5/2019 Project 3 5/6/2019 What i need to do is break each set of dates, by project, up to a max of 3 groups.(essentially splitting the project duration into 3rds) The end result would look like this.
Project # Dates worked Project 1 7/4/2019 1 Project 1 7/5/2019 1 Project 1 7/6/2019 2 Project 1 7/7/2019 2 Project 1 7/8/2019 3 Project 1 7/9/2019 3 Project 2 8/10/2019 1 Project 2 8/11/2019 2 Project 3 5/4/2019 1 Project 3 5/5/2019 2 Project 3 5/6/2019 3 Does this help?
