topic Re: Getting next date from dynamic indexing in DAX Commands and Tips

Getting next date from dynamic indexing

Anonymous — Fri, 24 Dec 2021 14:12:25 GMT

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

Re: Getting next date from dynamic indexing

amitchandak — Fri, 24 Dec 2021 14:38:13 GMT

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

Re: Getting next date from dynamic indexing

v-angzheng-msft — Thu, 30 Dec 2021 05:18:20 GMT

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.

Re: Getting next date from dynamic indexing

wdx223_Daniel — Thu, 30 Dec 2021 07:52:52 GMT

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] )

Re: Getting next date from dynamic indexing

Anonymous — Mon, 03 Jan 2022 11:21:33 GMT

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_workorderid	date_in	RowNum
id_1	22/12/2021 13:53	1
id_1	22/12/2021 13:45	2
id_1	22/12/2021 13:42	3
id_1	22/12/2021 13:42	4
id_1	22/12/2021 13:42	5
id_1	22/12/2021 13:40	6
id_1	2/12/2021 18:12	7
id_1	2/12/2021 10:27	8
id_1	2/12/2021 9:10	9
id_1	2/12/2021 9:10	10
id_1	2/12/2021 9:10	11
id_1	30/11/2021 15:49	12
id_1	30/11/2021 15:49	13
id_1	30/11/2021 15:49	14
id_1	30/11/2021 15:49	15
id_1	25/11/2021 22:18	16
id_1	25/11/2021 20:05	17
id_1	25/11/2021 20:05	18
id_1	25/11/2021 19:46	19
id_1	25/11/2021 19:45	20
id_1	25/11/2021 19:45	21
id_2	10/9/2021 16:15	1
id_2	26/8/2021 4:24	2
id_2	10/8/2021 17:13	3
id_2	10/8/2021 17:12	4
id_2	10/8/2021 17:11	5
id_2	10/8/2021 11:04	6
id_2	8/8/2021 15:15	7
id_2	8/8/2021 15:15	8
id_2	5/8/2021 16:50	9
id_2	5/8/2021 14:57	10

Re: Getting next date from dynamic indexing

Anonymous — Mon, 03 Jan 2022 11:17:25 GMT

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