Forum Discussion
talita19ny
4 years agoFrequent Visitor
Incorrect Averages when Calculating Negative Numbers
Hi, I would like some help to solve an issue Im facing when trying to average the totals in a Matrix. The issue arises when there are negative values to be calculated. What I am calculating is: ...
daXtreme
4 years agoSolution Sage
You should change the measure to this. I've not tested it, so if there are any problems, look closely at the formula, there might be typos in there or something trivial.
Team CashFlow Avg =
IF( ISFILTERED( Dates[Fiscal Year & Week]),
[Team Cash Flow],
// You should revise the calculation of UntilDate
// because the code you have does not correspond
// to what you're describing in your post...
VAR UntilDate =
// Get the last day of the month before the current one.
CALCULATE(
MAX( PREVIOUSMONTH( Dates[Date] ) ),
Dates[Date] = TODAY(),
// If you've got a proper date table marked as such,
// you don't need to remove filters. They'll be
// auto-removed by the engine. But it doesn't hurt
// to leave this in.
REMOVEFILTERS( Dates )
)
var Result =
AVERAGEX(
CALCULATETABLE(
DISTINCT ( Dates[Fiscal Year & Week]),
// Notice the <= instead of <
Dates[Date] <= UntilDate,
// Same remark to REMOVEFILTERS as above applies...
REMOVEFILTERS( Dates )
),
[Team Cash Flow]
)
return
Result
)