Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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:
Hello,
For anyone having the same problem as me, the solution I found was to use CALCULATE with two filters in the following way:
@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 |
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.
@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?
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
60 | |
43 | |
40 |