Forum Discussion

kalkhudary's avatar
kalkhudary
Icon for Helper IV rankHelper IV
2 years ago
Solved

Logic to group rows as one batch based on created date and Rank

Hello Community,    I am trying to find a solution for a scenrio where I need to dax couple of columns (1) which will help me group batches based on the created on date of the batch where for any b...
  • sevenhills's avatar
    2 years ago

    I cannot match your expected data as weeknum is giving the same for your rows 2 and above.

    I created similar dataset and able to get these results 

    Using Dax Columns:

     

    Rank Column = RANKX ( 'Table', WEEKNUM('Table'[Date]),,ASC, Dense)
    
    Batch Name Column = "Batch " & [Rank Column]
    
    Current Batch Column = If ( [Rank Column] = MAX (  'Table'[Rank Column]  ) , "Yes", "No")

    I think it will be good to have measures for this scenario, so providing the syntax for that also:

    Rank Measure = RANKX( allselected('Table'), WEEKNUM(  CALCULATE( max('Table'[Date]))) ,,asc, dense)
    
    Batch Name Measure = "Batch " & [Rank Measure]
    
    
    
    Current Batch Measure = 
    var _Max = MAXX ( all('Table'),  [Rank Measure] )
    RETURN If ( [Rank Measure] = _Max , "Yes", "No")

     

     

    If the source data and your expectation is per your post, then adjust to your needs the weeknum portion of DAX. 

     

    Hope it helps!