Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
T_von_Axt
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION
T_von_Axt
Helper I
Helper I

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.

View solution in original post

6 REPLIES 6
T_von_Axt
Helper I
Helper I

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
Super User
Super User

@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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Not applicable

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

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
Not applicable

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.