Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Getting next date from dynamic indexing

Hello,

 

In the below images, i'm trying to extract the datediff between some dates and then get a rolling sum of it, for every id. 

In the left table we have a date column sorted, which corresponds to the status column (2nd column). In the right table we have the same table filtered for specific status. Then I have created a dynamic measure called row_num, which as you see, is a dynamically adjusted to provide the index. 

My logic is to create a date_out calculated column, where it contains the value of date_in but row_num = row_num + 1. Then get the datediff of date_in, date_out of each row and finally get a rolling sum of datediff. 

 
RowNum = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( wo_dates ),
        COUNTROWS (
            FILTER (
                wo_dates,
                wo_dates[date_in] <= EARLIER ( wo_dates[date_in] )
                && wo_dates[dot_workorderid] = EARLIER (wo_dates[dot_workorderid]  )
            )
        )
    )
)

This is the code for Row_Num. It creates a circular dependency. 
Any suggestions are welcome

 

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,Can you share sample data and sample output in table format?

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.
    It makes it easier to give you a solution.



    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago
    date_out=
MAXX (
    FILTER (
        ALLSELECTED ( wo_dates ),
        COUNTROWS (
            FILTER (
                wo_dates,
                wo_dates[date_in] < EARLIER ( wo_dates[date_in] )
                && wo_dates[dot_workorderid] = EARLIER (wo_dates[dot_workorderid]  )
            )
        )
    ),wo_dates[date_in]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the reply. The specific formula returns the max date.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi thanks for your replies. 

    I'm looking for a solution that will give me the specific result

     

    The diff_in_minutes created column is basically the difference from the previous date entry, which is the metric im looking for. Consider that the dataset contains many id's.

     

    I've attached a small sample that contains the data above without the last column.

    dot_workorderiddate_inRowNum
    id_122/12/2021 13:531
    id_122/12/2021 13:452
    id_122/12/2021 13:423
    id_122/12/2021 13:424
    id_122/12/2021 13:425
    id_122/12/2021 13:406
    id_12/12/2021 18:127
    id_12/12/2021 10:278
    id_12/12/2021 9:109
    id_12/12/2021 9:1010
    id_12/12/2021 9:1011
    id_130/11/2021 15:4912
    id_130/11/2021 15:4913
    id_130/11/2021 15:4914
    id_130/11/2021 15:4915
    id_125/11/2021 22:1816
    id_125/11/2021 20:0517
    id_125/11/2021 20:0518
    id_125/11/2021 19:4619
    id_125/11/2021 19:4520
    id_125/11/2021 19:4521
    id_210/9/2021 16:151
    id_226/8/2021 4:242
    id_210/8/2021 17:133
    id_210/8/2021 17:124
    id_210/8/2021 17:115
    id_210/8/2021 11:046
    id_28/8/2021 15:157
    id_28/8/2021 15:158
    id_25/8/2021 16:509
    id_25/8/2021 14:5710