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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

       

      _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)
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 

         

        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?