Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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,
    Kelly
    Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI Anonymous 

 

rank Measure = 

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