Finding largest consecutive weeks window

[abhishekjangid]

Hi all,
I am trying to find out largest consecutive weeks window. I have a Date table something like this:
TableName: DATE DIMENSION

Year	Week
2021	3
2021	3
2021	4
2021	5
2022	7
2022	7
2022	8
2022	8
2022	9
2022	10

I get this table when I drill down from a summarized chart. Now I want to find out largest consecutive window in this data.
For example: In above table, largest window is 4 (2022(7), 2022(8), 2022(9), 2022(10))

I am able to achieve the result using python script but wanted to know if it is possible via DAX query.

This is how python code looks like:

##############################################################################
### Figure out maximum consecutive week window
##############################################################################
max_window_till_now = 0
prev = None
current_max = 0

for index, row in dataset.iterrows():
    if prev is None:
        current_max += 1
        prev = row
        continue
    
    # If years are not same or week difference is greater than 1 then weeks are not consecutive
    if (
        prev['Year'] != row['Year'] or
        prev['Week'] < row['Week'] - 1
    ):
        max_window_till_now = max(max_window_till_now, current_max)
        current_max = 1
    
    # If years are same and week difference if equals to 1 then weeks are consecutive
    elif prev['Week'] == row['Week'] - 1:
        current_max += 1

    prev = row
    
max_window_till_now = max(max_window_till_now, current_max)
##############################################################################

Please help me out here.

[abhishekjangid]

@Jihwan_Kim Please consider the example given as complete dataset.
One Date table with these entries, that's it.

[Jihwan_Kim]

Hi,

I am not sure how your dataset looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file, and each measure is numbered for easy recognizing the steps.

I hope the below sample can provide some ideas on how to create a solution for your dataset.