Incorrect Averages when Calculating Negative Numbers

[talita19ny]

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: 

 - The monthly totals since 2019 then averaging the grand total , divided by the amount of months. 

 - Months that closed in the negative need to be included in the calculation to show which months closed with loss / profit. 

 - Only completed months should be included in the calculation. So when querying the data today, I am only seeing data from Jan 2019 to Jul 2022. Tomorrow, September 1, I will see data from Jan 2019 to Aug. 2022.

I was able to get help with the date portion and the DAX formula is calculating only up to last month. However, when a metric has a  month with negative amount, the average is completely thrown off. 

The screen print below shows my Matrix, and the Last column is the average. There are no filters applied and only the last row  (Team Cash Flow) has negative values. The Average should be 467,909 and it's showing 105,787. 

Even though the Solo Cash Flow row has no past negative months, I suspect the December 2022 column is also throwing the calculation off. It's showing 405,749 and should be 1,793,323. 

I am using the following DAX formula and its the same for the SOLO Cashflow.. except the measure is Solo Cash Flow instead of Team Cash Flow:

Team CashFlow Avg =

VAR ResultSum =

        CALCULATE( [Team Cash Flow] )

VAR UntilDate = DATE( YEAR( TODAY() ) , MONTH(TODAY() ) -1, DAY(TODAY()))

VAR resultAvg =

        AVERAGEX(

            FILTER(

                 ADDCOLUMNS(

                         Calculatetable( distinct ( Dates[Fiscal Year & Week]), Dates[Date] < UntilDate),

                         "@Sum", CALCULATE( [Team Cash Flow])

                       ),

                      [@Sum]

                 ),

                 [@Sum]

        )

Return

        IF( ISFILTERED(Dates[Fiscal Year & Week]), ResultSum, resultAvg)

Obs: I created a new Matrix just to calculate the averages because I didnt know if there was a way to incorporate an average column in the primary Matrix. 

In the example below I've used filters and the averages are even crazier. The correct averages would be -1,380 and -2,289 consecutively and its showing -168 and -187.

The Average Solo and TeamAverage  Cashflow DAX measures are calculations of other measures:

These measures are pulling data from the Fact Table (Unit Cash Flow), which has a relationship with the Date table:

I've exported the spreadsheet from my view with the data for easier analysis but couldnt figure out how to attach it to this post. It says .xls is not supported, so I copied the information below. 

I appreciate any help on this. 

Thank you!

[daXtreme]

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
)

[talita19ny]

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

[daXtreme]

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...

[talita19ny]

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.