Dax 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

I will need the dax to generate this column colored in purple

Date	PB-Number	Name	Batched Date	Batch Name	Rank	Current Batch ?
2-Oct-23	PB-1003	EFT - PB-1003-10/02/2023	10/2/2023 10:15 a.m.	Batch 1	1	No
2-Oct-23	PB-1004	Cheque - PB-1004-10/05/2023	10/5/2023 9:15 a.m.	Batch 1	1	No
16-Oct-23	PB-1005	EFT - PB-1005-10/16/2023	10/16/2023 10:15 a.m.	Batch 2	2	No
21-Oct-23	PB-1006	Cheque - PB-1006-10/21/2023	10/21/2023 9:15 a.m.	Batch 2	2	No
24-Oct-23	PB-1007	EFT - PB-1007-10/24/2023	10/24/2023 10:15 a.m.	Batch 3	3	Yes
27-Oct-23	PB-1008	Cheque - PB-1008-10/27/2023	10/27/2023 9:15 a.m.	Batch 3	3	Yes

I tried multiple ways but still failing to get the right outcome. Any help will be appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Next batch date = CALCULATE(MIN(Data[Batched Date]),FILTER(Data,Data[Batched Date]>EARLIER(Data[Batched Date])&&LEFT(Data[Name],6)="Cheque"))

Previous batch date = CALCULATE(max(Data[Batched Date]),FILTER(Data,Data[Batched Date]<EARLIER(Data[Batched Date])&&LEFT(Data[Name],3)="EFT"))

Days diff = if(LEFT(Data[Name],3)="EFT",1*([Next batch date]-[Batched Date]),1*([Batched Date]-[Previous batch date]))

Type = LEFT(Data[Name],SEARCH(" ",Data[Name])-1)

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

Batch name = "Batch "&Data[Rank]

Hope this helps.

[Ahmedx · Accepted solution]

try this

[kalkhudary]

@Ashish_Mathur The solution of Ahmed did work but I was looking for a dax logic for the solution more than a table to connect to. If you had a chance to check why the logics are not working if we are basing them on the Date rather than the Batched date. So curious to learn the reason, what's wrong in this context.

[Ashish_Mathur]

Think over the logic for the final time and state that very clearly.  Share the download link of the PBI file and show the expected result very clearly.

[Ahmedx · Accepted solution]

pls try yhis

[kalkhudary]

@Ahmedx The variables worked great! Thanks for the quick turnaround.

[kalkhudary]

@Ashish_Mathur I just realized something when setting the logics. It is somehow not giving me the right difference. I am using the Date instead of Batched date as the batched date field might not be reflected on time. 

I have used the same logics that you posted. Any idea why the date diff is reflecting wrong.

https://app.powerbi.com/groups/me/reports/4d79d3cf-2b0b-41f6-a6d7-35dc8490fa10/ReportSection?experie... 

[Ashish_Mathur]

Try the solution posted by the other contributor.  Does that work?

[kalkhudary]

@Ashish_Mathur Thank you so much for this solution. I tried it and It worked.

@Ahmedx I also tried your method and It works Super! Thanks, you made my day 🙂

[Ashish_Mathur]

You are welcome.

[Ahmedx · Accepted solution]

try this

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Next batch date = CALCULATE(MIN(Data[Batched Date]),FILTER(Data,Data[Batched Date]>EARLIER(Data[Batched Date])&&LEFT(Data[Name],6)="Cheque"))

Previous batch date = CALCULATE(max(Data[Batched Date]),FILTER(Data,Data[Batched Date]<EARLIER(Data[Batched Date])&&LEFT(Data[Name],3)="EFT"))

Days diff = if(LEFT(Data[Name],3)="EFT",1*([Next batch date]-[Batched Date]),1*([Batched Date]-[Previous batch date]))

Type = LEFT(Data[Name],SEARCH(" ",Data[Name])-1)

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

Batch name = "Batch "&Data[Rank]

Hope this helps.