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 ...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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's avatar
Fluffy_Skye
Frequent Visitor
3 years ago

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.