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

[kalkhudary]

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 batch that falls within a week of the earlier created on date, it will be grouped as one batch and will be named batch 1 and to identify that the upcoming one will be batch 2 then 3 then 4 etc....

This is the data I have. For example PB-1003 and PB-1004 will be Batch 1 as the rule applies that they fall in same week starting from Oct 2  and PB-1005 and PB-1006 will be batch 2 as the rule applies that they fall within the same week starting from 16 Oct and PB-1007 and PB-1008 will be batch 3 as they fall within a week starting October 24.

Then I need a second column to rank the batches.  Then a third column that identifies if the batch is the current or not based on the last issued batch. However, I need to batch based on Date and NOT Batched Date as this field is not reliable.

I will need the dax to generate the columns colored in purple

Date	PB-Number	Name	Batched Date	Batch Name	Rank	Current Batch ?
10-02-2023	PB-1003	EFT - PB-1003-10/02/2023	10/2/2023 17:25 P.M.	Batch 1	1	No
10-02-2023	PB-1004	Cheque - PB-1004-10/02/2023	10/2/2023 17:08 P.M.	Batch 1	1	No
10-23-2023	PB-1005	EFT - PB-1005-10/23/2023	10/23/2023 17:25 P.M	Batch 2	2	No
10-25-2023	PB-1006	Cheque - PB-1006-10/25/2023	10/25/2023 17:00 P.M.	Batch 2	2	No
10-26-2023	PB-1007	EFT - PB-1007-10/26/2023	10/26/2023 16:15 P.M.	Batch 3	3	Yes
10-27-2023	PB-1008	Cheque - PB-1008-10/27/2023	10/27/2023 15:00 P.M.	Batch 3	3	Yes

This is what is showing in the results of the Daxes. The Next batch date is not reflecting or the 1st row correctly, It should be 10/02/2023 and for the previous batch date The second row should reflect 10/02/2023. Days diff results is giving me wrong calculations so the rank logic is turning wrong too. 

I am stuck as I really need the logics method for my report generation.

A community person helped me Dax the below but I may be doing something wrong that I am not getting the results right.

Next batch date = CALCULATE(Min(Sheet1[Created On]),FILTER(Sheet1,Sheet1[Created On]>EARLIER(Sheet1[Created On])&&LEFT(Sheet1[Name],6)="Cheque"))

Previous batch date = CALCULATE(MAX(Sheet1[Created On]),FILTER(Sheet1,Sheet1[Created On]<EARLIER(Sheet1[Created On])&&LEFT(Sheet1[Name],3)="EFT"))

Days diff = if(LEFT(Sheet1[Name],3)="EFT",1*(Sheet1[Created On]-[Previous batch date]),1*([Next batch date]-Sheet1[Created On]))

Rank = if([Days diff]<=7,CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[Batched Date]<=EARLIER(Sheet1[Batched Date])&&Sheet1[Name]=EARLIER(Sheet1[Name]))),BLANK())

[sevenhills · Accepted solution]

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!