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

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

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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

    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'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.