Forum Discussion

Fluffy_Skye's avatar
Fluffy_Skye
Frequent Visitor
3 years ago

How can I turn this into a loop?

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!

6 Replies

  • 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's avatar
      Fluffy_Skye
      Frequent Visitor

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

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User

        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: