Forum Discussion

Jessica07's avatar
Jessica07
Frequent Visitor
3 years ago
Solved

Rank changing based on date for each sub-group

I have a table containing data of ProjectID and each ProjectID has multiple periods. Each ProjectID is like a sub-group in my data in 'ProjectID' column. Each ProjectID has multiple period numbers that are shown in a column called 'RMS.Allocation.PeriodNumber'. 

I want to always get the lasted period for each project when filtering the date range. This means the period rank needs to change along with the date period changes. How can I do it? Appreciate your help in advance!

 

 

Need Help
  • andhiii079845's avatar
    andhiii079845
    3 years ago

    If the Periodnumber is no measure, i only know that you have to create one first:

    Measure = sum('Table'[PeriodNumber])
     
    And than use rankx
    Measure 2 = RANKX(ALLEXCEPT('Table','Table'[ProjectID]),CALCULATE([Measure]),,DESC)
     
    Perhaps someone know how to avoid to create the first measure? 

2 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    If the Periodnumber is no measure, i only know that you have to create one first:

    Measure = sum('Table'[PeriodNumber])
     
    And than use rankx
    Measure 2 = RANKX(ALLEXCEPT('Table','Table'[ProjectID]),CALCULATE([Measure]),,DESC)
     
    Perhaps someone know how to avoid to create the first measure?