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: ...
| SOLO Cash Flow | TEAM Cash Flow | Period |
| $1,785,944 | $610,066 | 1/1/2019 0:00 |
| $1,519,213 | $346,255 | 2/1/2019 0:00 |
| $1,913,704 | $737,826 | 3/1/2019 0:00 |
| $1,802,937 | $627,059 | 4/1/2019 0:00 |
| $1,902,039 | $726,161 | 5/1/2019 0:00 |
| $1,752,226 | $579,268 | 6/1/2019 0:00 |
| $1,526,589 | $350,711 | 7/1/2019 0:00 |
| $2,004,640 | $828,762 | 8/1/2019 0:00 |
| $1,631,344 | $458,386 | 9/1/2019 0:00 |
| $1,899,855 | $726,897 | 10/1/2019 0:00 |
| $1,636,406 | $459,899 | 11/1/2019 0:00 |
| $1,552,838 | $376,960 | 12/1/2019 0:00 |
| $3,349,792 | $3,349,792 | 1/1/2020 0:00 |
| $3,113,284 | $3,113,284 | 2/1/2020 0:00 |
| $3,667,244 | $3,667,244 | 3/1/2020 0:00 |
| $3,191,962 | $3,191,962 | 4/1/2020 0:00 |
| $3,028,246 | $3,028,246 | 5/1/2020 0:00 |
| $3,218,624 | $3,218,624 | 6/1/2020 0:00 |
| $3,397,591 | $3,397,591 | 7/1/2020 0:00 |
| $3,538,810 | $3,538,810 | 8/1/2020 0:00 |
| $3,639,035 | $3,639,035 | 9/1/2020 0:00 |
| $4,125,286 | $4,125,286 | 10/1/2020 0:00 |
| $3,342,474 | $3,342,474 | 11/1/2020 0:00 |
| $3,505,861 | $3,505,861 | 12/1/2020 0:00 |
| $609,378 | ($1,695,235) | 1/1/2021 0:00 |
| $353,209 | ($1,963,735) | 2/1/2021 0:00 |
| $1,349,859 | ($957,674) | 3/1/2021 0:00 |
| $1,039,196 | ($1,265,417) | 4/1/2021 0:00 |
| $1,021,232 | ($1,283,381) | 5/1/2021 0:00 |
| $1,171,310 | ($1,121,623) | 6/1/2021 0:00 |
| $916,754 | ($1,388,488) | 7/1/2021 0:00 |
| $1,232,042 | ($1,072,571) | 8/1/2021 0:00 |
| $1,067,585 | ($1,238,907) | 9/1/2021 0:00 |
| $879,874 | ($1,425,368) | 10/1/2021 0:00 |
| $537,027 | ($1,768,215) | 11/1/2021 0:00 |
| $796,032 | ($1,496,901) | 12/1/2021 0:00 |
| $350,581 | ($1,831,357) | 1/1/2022 0:00 |
| $460,338 | ($1,723,570) | 2/1/2022 0:00 |
| $1,014,555 | ($1,167,120) | 3/1/2022 0:00 |
| $808,553 | ($1,369,515) | 4/1/2022 0:00 |
| $531,417 | ($1,640,811) | 5/1/2022 0:00 |
| $456,408 | ($1,715,820) | 6/1/2022 0:00 |
| $471,583 | ($1,700,645) | 7/1/2022 0:00 |
| $473,477 | ($1,698,751) | 8/1/2022 0:00 |
| ($3,076,247) | ($5,247,962) | 12/1/2022 0:00 |
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.