count of frequency of overlaps based on time intervals

[Anonymous]

Hi, I would like to count the number ofoverlaps of 2 variables (drug 1 and drug 2) by duration; where the interval of drug 1 <90 days and interval of drug 2 <30 days.

In other words, how many occurance of drug 2 between the 90 days window of drug 1

Thank you so much and your help will be grealy appreciated

regards,

Fady

Note: The following measure was created but it did not calculate the proper frequency of overlap

CoAdmin Logic_MEASURE =

VAR vByeUswe = CALCULATETABLE(SUMMARIZE('Transformed Table', 'Transformed Table'[Date]),'Transformed Table'[Drug] = "2", 'Transformed Table'[Disp Inrtevals]<=30, 'Transformed Table'[Disp Inrtevals]>0)

VAR vIns = CALCULATETABLE(SUMMARIZE('Transformed Table', 'Transformed Table'[Date]), 'Transformed Table'[Drug] = "1", 'Transformed Table'[Disp Inrtevals]<=90, 'Transformed Table'[Disp Inrtevals]>0)

VAR Result = COUNTROWS(UNION(vByeUswe, vIns))

RETURN

Result

[Anonymous]

data sample

Index	ID	Drug	DispDATE	Inrtevals	Overlap logic
0	1	2	9/09/2012	28	2
1	1	2	7/10/2012	28	2
2	1	2	18/11/2012	42	4
3	1	2	27/01/2013	70	4
4	1	2	10/03/2013	42	4
5	1	1	21/04/2013	42	1
6	1	2	5/05/2013	14	2
7	1	1	5/05/2013	14	1
8	1	2	2/06/2013	28	2
9	1	2	16/06/2013	14	2
10	1	1	28/07/2013	42	1
11	1	2	11/08/2013	14	2
12	1	1	11/08/2013	14	1
13	1	2	16/06/2013	14	2

[AntrikshSharma]

@Anonymous What was wrong in your result? Please attach sample data, desired/correct result and how you are trying to visualize it.

[Anonymous]

I managed to build a count of seq function in excel as shown above using nested if (as shown above) to count sequence of 1 followed by 2. Is it possible to build a similar measure in Dax

Thank you in advance and your help will be greatly appreciated

Fady

[Anonymous]

The Desired output : calculate the count of overlaps of logic 2 (drug 2 and interval =<30) happening in between logic 1 (drug 1 and interval =<90) for the same ID...In other words, we want to identify the sequential pattern of 1,2,1 or 1,2,2,1 in the table and count those overlaps 

Thank you so much.

Fady

Note: : the previous measure shown earlier created a count of all rows in calculated table joining logic 2 and logic 1 but ignoring the dersired sequence of intervals, resuling in overestimating the occurences