cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

11 REPLIES 11
Highlighted
Super User IV
Super User IV

@TO_CB 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
  __MaxStatus

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Sorry 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 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

 

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 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

 

 

Does this help?

 

Highlighted
Community Champion
Community Champion

Hi @TO_CB 

I wouls use MOD in ths case.

Lets say you have the output of your rank function in 

Var _rank = RANK(......)

Return MOD(_rank,3)+1

 

This will give you the output only as 1, 2 and 3


Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Highlighted

@pranit828   I'm sorry, I'm not following. Can you explain in more detail please? 

Highlighted
Community Champion
Community Champion

HI @TO_CB 

 

rank Measure = 

var _rank = RANKX( 'Table','Table'[Date],,ASC)
return MOD(_rank,3)+1

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Highlighted

@pranit828  im sorry to say that did not work.

 

With a live example, this is the result of your formula 

Project #Dates worked 
Project 1Friday, June 26, 20202
Project 1Saturday, June 27, 20201
Project 1Sunday, June 28, 20201
Project 1Monday, June 29, 20201
Project 1Tuesday, June 30, 20203
Project 1Wednesday, July 1, 20201
Project 1Thursday, July 2, 20201
Project 1Friday, July 3, 20202
Project 1Saturday, July 4, 20202
Project 1Sunday, July 5, 20201
Project 1Monday, July 6, 20203
Project 1Tuesday, July 7, 20202
Project 1Wednesday, July 8, 20203
Project 1Thursday, July 9, 20201
Project 1Friday, July 10, 20202
Project 1Saturday, July 11, 20201
Project 1Sunday, July 12, 20201
Project 1Monday, July 13, 20201
Project 1Tuesday, July 14, 20203
Project 1Wednesday, July 15, 20201
Project 1Thursday, July 16, 20201
Project 1Friday, July 17, 20203
Project 1Saturday, July 18, 20201
Project 1Sunday, July 19, 20201
Project 1Monday, July 20, 20201
Project 1Tuesday, July 21, 20201
Project 1Wednesday, July 22, 20203
Project 1Thursday, July 23, 20203
Project 1Friday, July 24, 20202
Project 1Saturday, July 25, 20203
Project 1Sunday, July 26, 20203
Project 1Monday, July 27, 20203

 

the result im looking for is below

Project #Dates worked 
Project 1Friday, June 26, 20201
Project 1Saturday, June 27, 20201
Project 1Sunday, June 28, 20201
Project 1Monday, June 29, 20201
Project 1Tuesday, June 30, 20201
Project 1Wednesday, July 1, 20201
Project 1Thursday, July 2, 20201
Project 1Friday, July 3, 20201
Project 1Saturday, July 4, 20201
Project 1Sunday, July 5, 20201
Project 1Monday, July 6, 20201
Project 1Tuesday, July 7, 20202
Project 1Wednesday, July 8, 20202
Project 1Thursday, July 9, 20202
Project 1Friday, July 10, 20202
Project 1Saturday, July 11, 20202
Project 1Sunday, July 12, 20202
Project 1Monday, July 13, 20202
Project 1Tuesday, July 14, 20202
Project 1Wednesday, July 15, 20202
Project 1Thursday, July 16, 20202
Project 1Friday, July 17, 20202
Project 1Saturday, July 18, 20203
Project 1Sunday, July 19, 20203
Project 1Monday, July 20, 20203
Project 1Tuesday, July 21, 20203
Project 1Wednesday, July 22, 20203
Project 1Thursday, July 23, 20203
Project 1Friday, July 24, 20203
Project 1Saturday, July 25, 20203
Project 1Sunday, July 26, 20203
Project 1Monday, July 27, 20203

 

Im trying to break the Projects duration into 3 parts... in chronological order.   So in the case above, the first 11 dates would be "1", the middle "2", and the latter "3"

Highlighted
Community Champion
Community Champion

Hi @TO_CB 

 

_number = 
var _cnt = rankx(allselected(Table1),CALCULATE ( MAX (Table1[Dates worked])),,ASC)
var _max = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(table1,Table1[Project #]))
var _d3 = _max/3
return CEILING(_cnt/_d3,1)

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Highlighted

@pranit828 

 

So as a measure, it doesn't seem to stop at 3.  I see values up into the hundreds.

 

because of the insight im looking to get, the best solution for this would be a calculated column..however when i add the formula in for my new column I get the error "A circular dependency was detected: Table1[Column]."

 

any thoughts?

 

Highlighted
Community Champion
Community Champion

Hi @TO_CB 

According to your sample my previous reply answers your question.

 

Probably you should put more time in making a good sample of your data when ask so that you get to the point reply.

 Try the below as a measure, column is preferred when there is a calculation done for each row.

 

_number = 
var _cnt = rankx(allselected(Table1),CALCULATE ( MAX (Table1[Dates worked]), allexcept(table1, table1[Project #])),,ASC)
var _max = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(table1,Table1[Project #]))
var _d3 = _max/3
return CEILING(_cnt/_d3,1)

 

 


Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


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