How can I turn this into a loop?

[Fluffy_Skye]

I am writing DAX for a custom column. Long story short: I have a column of dates. My goal is to separate these dates based on 60 day interval rule and identify the starting date of the interval that each date falls in. For example, suppose I have the following list of dates.

1/1/2023
1/2/2023
5/1/2023
5/2/2023
5/30/2023
1/1/2024
2/2/2024
7/30/2024
8/30/2024

The custom column I'm looking for should produce the following result (the column on the right)

1/1/2023    1/1/2023
1/2/2023    1/1/2023
5/1/2023    5/1/2023
5/2/2023    5/1/2023
5/30/2023   5/1/2023
1/1/2024    1/1/2024
2/2/2024    1/1/2024
7/30/2024   7/30/2024
8/30/2024   7/30/2024

The first date is always going to be the first date in the first 60 day interval. The second date 1/2/2023 is within 60 days of the first date, so it falls into the first 60 day interval as well. So the second row I'm also expecting 1/1/2023 in my custom column as it is the starting date of the 60 day interval that the second date falls into. Now, the third date 5/1/2023 is more than 60 days apart from the first date, and so it becomes the first day in the second 60 day interval. The forth date 5/2/2023 is withink 60 days of the third date, so it is also in the second interval and should return 5/1/2023 in the custom column.

Despite my best effort, I can only think of this in an iterative fashion. I've looked up a lot of articles and youtube videos on how to simulate a while loop in DAX, but those only showcased simple examples.

Now, if I simply write out the code for each iteration, I would get my desired result, but it won't be scalable. My code is listed below. curtable contains a column of dates and ranktable is curtable with an additional column ranking the dates in order.

result column =
var curtable = ...
var ranktable = ...
var min1date = MINX(ranktable, [from_service_date])
var batch1 = COUNTX(FILTER(curtable, DATEDIFF(min1date, [from_service_date], DAY) < 60), [from_service_date])
var min2date = MINX(FILTER(ranktable, [rank] > batch1), [from_service_date])
var batch2 = COUNTX(FILTER(curtable, DATEDIFF(min2date, [from_service_date], DAY) < 60), [from_service_date])
var min3date = MINX(FILTER(ranktable, [rank] > batch2), [from_service_date])
var batch3 = COUNTX(FILTER(curtable, DATEDIFF(min3date, [from_service_date], DAY) < 60), [from_service_date])
var min4date = MINX(FILTER(ranktable, [rank] > batch3), [from_service_date])
var batch4 = COUNTX(FILTER(curtable, DATEDIFF(min4date, [from_service_date], DAY) < 60), [from_service_date])
var min5date = MINX(FILTER(ranktable, [rank] > batch4), [from_service_date])
var batch5 = COUNTX(FILTER(curtable, DATEDIFF(min5date, [from_service_date], DAY) < 60), [from_service_date])
return
IF(DATEDIFF(min1date, [from_service_date], DAY) < 60, min1date,
IF(DATEDIFF(min2date, [from_service_date], DAY) < 60, min2date,
IF(DATEDIFF(min3date, [from_service_date], DAY) < 60, min3date,
IF(DATEDIFF(min4date, [from_service_date], DAY) < 60, min4date,
IF(DATEDIFF(min5date, [from_service_date], DAY) < 60, min5date
)))))

As you can see, minxdate is the first date in the x-th interval. I am able to get the correct result this way but if the date range is much larger, I would need much more than just 5 batches like I coded above.

Please help!

[FreemanZ]

hi @Fluffy_Skye 

not sure if i fully get you, try

1) create a calculated table like:

dates = CALENDAR(date(2023,1,1), date(2023,12,31))

2) add a calculated column like:

rank = MOD(RANKX(dates, [Date],,ASC), 60)

it worked like:

[Fluffy_Skye]

Hi, I've edited my question. I hope it is more clear!

[FreemanZ]

hi @Fluffy_Skye 

then try to add a calculated column like:

Column = MIN(dates[Date]) + INT(DIVIDE ([date]-MIN(dates[Date]), 60))*60

 it worked like:

[Fluffy_Skye]

Hi @FreemanZ 

Thanks for the response but that's not what I'm looking for. In your example, my desired result is as follows.

1/1/2023    1/1/2023
1/2/2023    1/1/2023
5/1/2023    5/1/2023
5/30/2023   5/1/2023
7/1/2023    7/1/2023
7/30/2023   7/1/2023
9/1/2023    9/1/2023
9/30/2023   9/1/2023

This is because 7/1/2023 is more than 60 days apart from 5/1/2023, so it becomes the first day in the third batch. Similarly, 9/1/2023 is more than 60 days apart from 7/1/2023, so it becomes the first day in the forth batch.

In particular, the result date should always come from the original date column, as it represents the first day in the batch.

[FreemanZ]

hi @Fluffy_Skye 

The logic is the same.

then try to add a calculated column:

Column = 
VAR BatchStart= MIN(ranktable[Date1]) + INT(DIVIDE (ranktable[date1]-MIN(ranktable[Date1]), 60))*60
VAR result =
MINX(
    FILTER(
        ranktable, 
        ranktable[date1] >= BatchStart
    ),
    ranktable[date1]
)
RETURN result

it worked like:

[Fluffy_Skye]

Hi @FreemanZ 

Thanks for the update. This method is my first attempt as well, but it doesn't provide the correct result. Specifically, it can only guarantee the correct identification of the second batch start date, but the third batch start date won't be correct if the dates are not structured perfectly.

Please see the example below, which is part of the actual data I'm working with. I've put the correct result in the third column and the result of your method in the second column. Now I think you could see the problem I'm running into, which is why it seems to me I have to iteratively find the first day in each batch.

6/1/2022       6/1/2022       6/1/2022
6/28/2022      6/1/2022       6/1/2022
7/30/2022      6/1/2022       6/1/2022
8/11/2022      8/11/2022      8/11/2022
9/20/2022      8/11/2022      8/11/2022
10/6/2022      10/6/2022      8/11/2022
10/12/2022     10/6/2022      10/12/2022
11/25/2022     10/6/2022      10/12/2022
11/29/2022     11/29/2022     10/12/2022
1/2/2023       11/29/2022     1/2/2023
2/7/2023       2/7/2023       1/2/2023
3/6/2023       2/7/2023       3/6/2023

As you can see, in your calculation, when it comes to the date 10/6/2022, you will get

var BatchStart = 6/1/2022 + 2 * 60
var result = 10/6/2022

That is, your method thinks that since 10/6/2022 is more than 120 days apart from 6/1/2022, so it must be in the third or more batch. However, since 10/6/2022 is within 60 days from 8/11/2022, it should belong to the second batch.