Forum Discussion
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: ...
First, you've got a bug in your code. One should never write things like
VAR UntilDate = DATE( YEAR( TODAY() ) , MONTH(TODAY() ) -1, DAY(TODAY()))
because if you're in January, your month will become 0 in this formula. What you should do instead is use the time-intel functions. ALWAYS, with the exception of custom calendars. On top of that, you'll be in trouble when you're on 31 of a month.
For instance, try to execute this piece of code in DAX Studio:
EVALUATE
{ date(2001, 2, 31) }and see what you're gonna get...
I see what you mean. .. I got 3/3/2001.
This was the solution a user gave me to calculate only closed months and exclude current and any future months from the average calculations. So I guess I have even more to work out now.