I'm looking for a record with double growth

[jeyare]

So, a speciall challenge is here.

Single table of data for the calculation (sample):

ID	Date	Branch	ValueX
1	1.1.2021	A	60
2	1.1.2021	B	40
3	1.1.2021	C	20
4	2.1.2021	A	30
5	2.1.2021	B	100
6	2.1.2021	C	50
7	3.1.2021	A	120
8	3.1.2021	B	30
9	3.1.2021	C	110
10	4.1.2021	A	30
11	4.1.2021	B	80
12	4.1.2021	C	10
13	5.1.2021	A	20
14	5.1.2021	B	210
15	5.1.2021	C	40

Target No. 1:

I need find for each record in column [ValueX], Filtered by column [Branch] and Date within column [Date] based on all of these conditions:

1. Double of the [ValueX], lets describe it as [DoubleValueXPOS] is each number what is >= than 2x of value in [ValueX]

2. I need take into account only nearest record what is NEWER than [Date] of the evaluated [ValueX]

3. for each the [Branch] independently

Finaly I need create a new Column [Doubling Days POS] where I will a Subtract of [Date] of my base number and discovered Double date by [DoubleValueX].

An example (described):

when ID = 1 contains Date = 1.1.2021; Branch = A; ValueX = 60

then

Nearest double for ID = 1 is ID =7 because:

it's same Branch

ValueX (ID7) >= than 2x of value in [ValueX] in (ID1)

Doubling Days = Date of ID7 - Date of ID1 = 2

An example (in table):

ID	Date	Branch	ValueX	Doubling Days POS
1	1.1.2021	A	60	2
2	1.1.2021	B	40	1
3	1.1.2021	C	20	1
4	2.1.2021	A	30	1
5	2.1.2021	B	100	3
6	2.1.2021	C	50	1
7	3.1.2021	A	120
8	3.1.2021	B	30	1
9	3.1.2021	C	110
10	4.1.2021	A	30
11	4.1.2021	B	80	1
12	4.1.2021	C	10	1
13	5.1.2021	A	20
14	5.1.2021	B	210
15	5.1.2021	C	40

---------------

Target No. 2 (similar but reverse order in Date and smalest than ...):

I need find for each record in column [ValueX], Filtered by column [Branch] and Date within column [Date] based on all of these conditions:

1. Double of the [ValueX], lets describe it as [DoubleValueXNEG] is each number what is <= than 2x of value in [ValueX]

2. I need take into account only nearest record what is OLDER than [Date] of the evaluated [ValueX]

3. for each the [Branch] independently

Finaly I need create a new Column [Doubling Days NEG] where I will a Subtract of [Date] of my base number and discovered Double date by [DoubleValueX].

An example (described):

Last possible Day is  Max ([Date]) = 5.1.2021; Branch = C ; ValueX = 40; ID =15

then

Nearest double for ID = 15 is ID =12 because:

it's same Branch

ValueX (ID12) <= than 2x of value in [ValueX] in (ID15)

Doubling Days = Date of ID15 - Date of ID7 = 1

An example (in table):

ID	Date	Branch	ValueX	Doubling Days POS	Doubling Days NEG
1	1.1.2021	A	60	2
2	1.1.2021	B	40	1
3	1.1.2021	C	20	1
4	2.1.2021	A	30	1	1
5	2.1.2021	B	100	3	1
6	2.1.2021	C	50	1	1
7	3.1.2021	A	120		2
8	3.1.2021	B	30	1
9	3.1.2021	C	110		1
10	4.1.2021	A	30		1
11	4.1.2021	B	80	1	1
12	4.1.2021	C	10	1	1
13	5.1.2021	A	20
14	5.1.2021	B	210
15	5.1.2021	C	40		1

Thx for a help

[v-shex-msft]

HI @jeyare,

Did MFelix 's solution help for your scenario? If this is a case, you can consider accepting this to help others who face similar scenarios to find it more quickly.
If not, you can feel free to post here with detailed information.
Regards,

Xiaoxin Sheng

[MFelix · Accepted solution]

Hi @jeyare ,

I understand the first target but however I'm confused about the negative.

I have created the following measures:

Doubling Days POS =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[Branch]; 'Table'[Date]; 'Table'[ID]; 'Table'[ValueX] );
            'Table'[Branch];
            'Table'[ID];
            'Table'[Date];
            'Table'[ValueX]
        );
        'Table'[ValueX]
            >= 2 * SELECTEDVALUE ( 'Table'[ValueX] )
            && 'Table'[Branch] = SELECTEDVALUE ( 'Table'[Branch] )
            && 'Table'[Date] > SELECTEDVALUE ( 'Table'[Date] )
    )
RETURN
    DATEDIFF (
        SELECTEDVALUE ( 'Table'[Date] );
        MINX ( temp_table; 'Table'[Date] );
        DAY
    )


Doubling Days NEG =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[Branch]; 'Table'[Date]; 'Table'[ID]; 'Table'[ValueX] );
            'Table'[Branch];
            'Table'[ID];
            'Table'[Date];
            'Table'[ValueX]
        );
        'Table'[ValueX]
            <= 2 * SELECTEDVALUE ( 'Table'[ValueX] )
            && 'Table'[Branch] = SELECTEDVALUE ( 'Table'[Branch] )
            && 'Table'[Date] < SELECTEDVALUE ( 'Table'[Date] )
    )
RETURN
    DATEDIFF (
        MAXX ( temp_table; 'Table'[Date] );
        SELECTEDVALUE ( 'Table'[Date] );
        DAY
    )

Check result below. My question is regarding the ID 8 , 13 and 14 since I'm not abble to return blank has you since you are comparing values with the double of the selected value everything single value. 

PBIX file attach.