MEASURE for sum over data after specific date which varies for different data groups

[Anonymous]

Comment: I use the data format dd.mm.yyyy

My data looks like the following: 

date	group	start_date	value1	value2	label
01.01.2020	A	23.07.2006	100	-5	big
01.04.2020	A	23.07.2006	120	20	big
01.07.2020	A	23.07.2006	230	110	big
01.10.2020	A	23.07.2006	210	-20	big
01.07.2020	B	10.04.2020	70	0	small
01.10.2020	B	10.04.2020	90	20	small
01.01.2020	C	01.12.2019	10	10	big
01.04.2020	C	01.12.2019	20	10	big
01.07.2020	C	01.12.2019	20	0	big
01.10.2020	C	01.12.2019	50	30	big

Some explanation about my data: 

For every group there are

• two fixed parameters: the start date and the label
• a date (which is always bigger or equal the start date and bigger or equal 01.01.2020)
• two values

My goal is to create a measure with the following: 

• for every group I want to find the earliest [date]  (called _start_calc_date) with [date]>=[start_date]+4months, i.e. : 
  A: 01.01.2020
  B: 01.10.2020
  C: 01.04.2020
• for every group I want to pick _num1 as the sum of the values of [Value1] at each _start_calc_date of the previous step  
  100 + 90 + 20
• I want to sum up all the values of [Value2] for dates after the _start_calc_date (and call this sum _sum_val2)
  20+110-20+0+30
• as a return I want to divide these two values _sum_val2/_num1 (or set the return to 0 if not possible)
• in my Dashboard I want to be able to use a label filter 

I work with a seperate date-file m_date with contains one column with the dates from 01.01.2019 until today. 
Here is my attempt (which doesn't work yet): 

measure=

VAR _begin_date = FIRSTDATE('table'[start_date])

VAR _lastdate = LASTDATE(m_date[Date])

VAR _start_calc_date =

CALCULATE(

FIRSTDATE('table'[date]),

FILTER('table' , 'table'[date] >= DATEADD('table'[_begin_date],4,month))

)

VAR _num1 =

CALCULATE(

SUMX(

FILTER('table' , 'table'[date] =_start_calc_date),

SUM('table'[Value1])

),

USERELATIONSHIP('table'[date],m_date[Date])

)

VAR _sum_val2 =

SUMX(

SUMMARIZE(

'table',

'table'[group],

"sum_v2",

CALCULATE(

SUMX(

FILTER('table' , 'table'[date] >_start_calc_date),

'table'[Value2]

)

)

),

[sum_v2]

)

RETURN 

DIVIDE(_sum_val2,_num1,0)

Thanks in advance for your help 🙂 ! (And please let me know if you need more information, this is my first question here 😊) 

[v-janeyg-msft]

Hi, @Anonymous 

Can you tell me why according to your logic, the date of group c is 2020.1.4 instead of 2020.1.1? And how you want to present the result?

Best Regards,
Community Support Team _ Janey

[Anonymous]

Hi Janey, 

I don't only have one "date" for each group. "date" refers to the date of our data (every three month). 
I don't want to use the data of the first 4 months after a group started. Group 4 started in december 2019, so the first data which I want to consider is the one from April 2020. 
Thank you for taking time to help me 🙂 
Best wishes

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Accordting to your description, I create s a sample.

You need to create the measures like these:

_start_calc_date = 
MINX (
    FILTER (
        ALL ( 'Table' ),
        [group] = SELECTEDVALUE ( 'Table'[group] )
            && [date]
                >= SELECTEDVALUE ( 'Table'[start_date] ) + 120
    ),
    [date]
)

num1 = 
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [group] = SELECTEDVALUE ( 'Table'[group] )
                && [date] =[_start_calc_date]
        ),
        [value1]
    )

sum_val2 = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        [group] = SELECTEDVALUE ( 'Table'[group] )
            && [date] > [_start_calc_date]
    ),
    [value2]
)

_num1 = SUMX(SUMMARIZE('Table',[group],"a",[num1],"b",[sum_val2]),[a])+0

_sum_val2 = SUMX(SUMMARIZE('Table',[group],"a",[num1],"b",[sum_val2]),[b])+0

_divide_two = DIVIDE([_sum_val2],[_num1])+0

Flag = IF(SELECTEDVALUE('Table'[date])=[_start_calc_date],1,0)

Below is my sample. Hope it helps.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Thankyou so much for the help! That looks very good already. My problem now is the "flag":

I have a visual in which I visualize the calculated value for each "label". If I put the filter "Flag=1" on it, no more data is shown to me.

[v-janeyg-msft]

@Anonymous  You don't have to use the flag, it is my idea. You can delete it in visual fiiter pane.

[v-janeyg-msft]

@Anonymous  I got it wrong at first because of your date format. I'll get back to you later.

[tamerj1]

Hi @Anonymous 

Thank you for this elegant description of you problem. Yet There are two things which are not clear to me. How did you calculate the date? How would you disply the result?

[Anonymous]

Hi tamerj1, 

I would like to use the result as a measure in a visual. The [date] data is from my data, I didn't calculate it.
 
Thank you for taking time to help me 🙂