topic Re: MEASURE for sum over data after specific date which varies for different data groups in DAX Commands and Tips

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

Anonymous — Thu, 28 Apr 2022 08:17:21 GMT

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

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

tamerj1 — Sat, 23 Apr 2022 10:50:35 GMT

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?

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

v-janeyg-msft — Tue, 26 Apr 2022 03:16:19 GMT

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

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

Anonymous — Thu, 28 Apr 2022 06:43:46 GMT

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

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

Anonymous — Thu, 28 Apr 2022 06:46:13 GMT

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 🙂

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

v-janeyg-msft — Thu, 28 Apr 2022 06:53:52 GMT

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

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

v-janeyg-msft — Thu, 28 Apr 2022 09:54:34 GMT

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

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

Anonymous — Fri, 29 Apr 2022 07:04:46 GMT

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.

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

v-janeyg-msft — Fri, 29 Apr 2022 07:07:57 GMT

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