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'm looking for a formula that uses sums as logic that we know in Excel. I want to add a calculated column (orange) in the table below. That sums values of column value if the column values of date and color are equal.
I once came across a formula with the EARLIER function, but I don't know the exact formula to get it to work.
Solved! Go to Solution.
Hint: You should never use EARLIER. There is a much better alternative and it's called VARIABLES.
// This is the formula for your column...
[Total Value] =
var CurrentDate = YourTable[Date]
var CurrentColor = YourTable[Color]
var Output =
sumx(
filter(
YourTable,
YourTable[Date] = CurrentDate
&&
YourTable[Color] = CurrentColor
),
YourTable[Value]
)
return
Output
I want to thank you both ( @daXtreme & @zerotyper ) for the quick and well-functioning response. I have been helped tremendously.
As a result of @daXtreme 's additional explanation, I naturally opt for his solution.
Thanks and have a nice weekend!
A word of caution.
Using CALCULATE in calculated column is a no-no. This slows DAX tremendously down even on moderate sized sets. I've seen many a time formulas like this that brought the engine down to a complete freeze.
@daXtreme Really thanks for your suggestion.
You are right, just use Variable and simply filter and Aggregation is better than Calculate in a calculated column.
The context transition is too heavy.
Hint: You should never use EARLIER. There is a much better alternative and it's called VARIABLES.
// This is the formula for your column...
[Total Value] =
var CurrentDate = YourTable[Date]
var CurrentColor = YourTable[Color]
var Output =
sumx(
filter(
YourTable,
YourTable[Date] = CurrentDate
&&
YourTable[Color] = CurrentColor
),
YourTable[Value]
)
return
Output
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
19 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
25 | |
10 | |
10 | |
9 | |
6 |