Forum Discussion
Distribute date ranges into groups automatically
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!
My apologies that my request was unclear. I don't use the forums often to request help, so forgive me for thinking I had explained what i was looking for thoroughly enough.
I have tried your measure, and its actually gotten worse in the number return.
I shall try to explain this more in depth.
The need: To break down a Projects duration (start to finish) into thirds. Identify which dates fall into which 3rd of each project. Durations can be anywhere from 1 day to hundreds of days long. We have a table with thousands of projects of varying durations. I have already created a table that shows the Project Number and each individual date the project was active. shown here
Example Table:
| 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 |
The request: Can anyone help me create a Calculated Column, either in DAX or in Power Query, to label each row as either the first, middle or latter third of a project. The result would look like below
Example Table 2: Desired Outcome
| Project # | Dates worked | Thirds |
| 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 hope this explanation is clearer than my original ask.
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.
Kelly