How do I create a column that is the sum of matching elements in a specific timeframe?

[T_von_Axt]

I have searched a lot before asking this question here but couldn't find anything related to my problem.

I am currently trying to get a specific value (let's call it "sum in reference period") to be generated in a new column.

I have different elements that are linked to a group (e.g. element A is from Group 1, element B is from Group 2, element C is from Group 3, etc.) and each element has the date when it was created in the following format: YYYYMM.

How do I create an additional column with the sum of all elements that are from the same group as the element in the row and that were created in the last 12 months from the element creation?

Let's say my current table looks like this:

| Element_Id | Creation_Date | Group |
| A          | 202101        | 1     |
| B          | 202101        | 2     |
| C          | 202101        | 3     |
| D          | 202102        | 1     |
| E          | 202001        | 1     |
| F          | 202002        | 1     |
| G          | 202003        | 1     |

And I want the result to be like this:

| Element_Id | Creation_Date | Group | Sum_In_Reference_Period |
| A          | 202101        | 1     | 4                       |
| B          | 202101        | 2     | 1                       | 
| C          | 202101        | 3     | 1                       |
| D          | 202102        | 1     | 3                       |
| E          | 202001        | 1     | 1                       |
| F          | 202002        | 1     | 2                       |
| G          | 202003        | 1     | 3                       |

Thank you in advance.

[T_von_Axt · Accepted solution]

Hello,

For anyone having the same problem as me, the solution I found was to use CALCULATE with two filters in the following way:

Result = CALCULATE(COUNT(Table[ELEMENT]), FILTER(Table, Table[GROUP] = earlier(Table[GROUP]) && earlier(Table[CREATION_DATE]) <= DATEADD(Table[CREATION_DATE], 12, MONTH)))

Why did I put "earlier(Table[CREATION_DATE]) <= DATEADD(Table[CREATION_DATE], 12, MONTH)))" instead of simply using "Table[CREATION_DATE] >= DATEADD(earlier(Table[CREATION_DATE], -12, MONTH))))" that is more direct?

Because DATE functions in DAX are super clunky and only accept columns as parameters, meaning that earlier(Table[CREATION_DATE]) is not a valid parameter for the function.

[smpa01]

@T_von_Axt  can you please explain what is the logic behind the following

Element_Id	Creation_Date	Group	desiredResult
A	202101	1	3
D	202102	1	3
E	202001	1	1
F	202002	1	2
G	202003	1	3

[T_von_Axt]

The desired result is the sum of all values that are from the same group and in the timeframe of until one year before the creation date of the element.

So for element A we have 4, because element A, E, F and G are from Group 1 and they have their creation date in the interval of one year before the creation date of element A. 

For element D we only have one, because despite it being from group 1, it is the single element that was created in the interval of one year before its creation date.

[Anonymous]

@T_von_Axt Try this:

Calc_Colum=var CurrentGroup='Table'[Group]

var CurrentDate='Table'[Creation_Date]

var tempTable=SUMMARIZE(FILTER(ALL('Table'),'Table'[Group]=CurrentGroup&&(CurrentDate-'Table'[Creation_Date])<=100),'Table'[Element_Id])

return COUNTROWS(tempTable)

[T_von_Axt]

Thank you for your answer.

Just one question: how do you point to the current group in that row? I found no way on how can I reference my current group in that row outside of Table[Group] = Table[Group], but this wouldn't have any effect right?

[Anonymous]

It is defined through variable thanks to row context in calculated columns; as a result value of variable is fixed and doesn't change during following calculations.