cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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 17/4/20191
Project 17/5/20191
Project 17/6/20192
Project 17/7/20192
Project 17/8/20193
Project 17/9/20193
Project 28/10/20191
Project 28/11/20192
Project 35/4/20191
Project 35/5/20192
Project 35/6/20193

 

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?

11 REPLIES 11

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 17/4/2019
Project 17/5/2019
Project 17/6/2019
Project 17/7/2019
Project 17/8/2019
Project 17/9/2019
Project 28/10/2019
Project 28/11/2019
Project 35/4/2019
Project 35/5/2019
Project 35/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 workedThirds
Project 17/4/20191
Project 17/5/20191
Project 17/6/20192
Project 17/7/20192
Project 17/8/20193
Project 17/9/20193
Project 28/10/20191
Project 28/11/20192
Project 35/4/20191
Project 35/5/20192
Project 35/6/20193

 

 

I hope this explanation is clearer than my original ask.

Hi  @TO_CB

 

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:

1.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors