[HELP] Matrix with Actual Sales + Forecasted Sales showing the wrong aggregation.

[raphazzz]

Team,

I'm creating a matrix to sum the sales to date and forecasted sales.

The table rows are correct but the aggregation is wrong. I couldn't figure out what I am missing in the code.

The current month is not actual because the month is not over (for June), it's an estimated month-end (which is different than the forecasted).

Apr - Actual Sales
May - Actual Sales
June - Estimate Month-End (which is based on current sales)
July - Forecasted at the beginning of the year
August - Forecasted at the beginning of the year
September- Forecasted at the beginning of the year

The total should be $199,583,804

zCombined Actual and Forecast2 = 
VAR LastActualSaleDate =
    CALCULATE ( MAX ( SalesReport[Date Sold] ), REMOVEFILTERS () )
VAR Actuals =
    IF (
        MAX ( SalesReport[Date Sold] ) = LastActualSaleDate,
        SalesReport[zEstimate IRCV],
        SalesReport[zAct. IRCV]
    )
VAR Future =
    CALCULATE (
        SUM ( 'Sales Projection 2023'[IRCV] ),
        FILTER ( 'a.Date Table', 'a.Date Table'[Date] > LastActualSaleDate )
    )

RETURN
   Future + Actuals

I have no idea what might be wrong. 

[rbriga]

My DAX formula wasn't correct.

Try a hybrid- your formula (which works) within the context of a SUMX:

zCombined Actual and Forecast2 = 
VAR LastActualSaleDate =
    CALCULATE ( MAX ( SalesReport[Date Sold] ), REMOVEFILTERS () )
SUMX(
	VALUES('a.Date Table'[Year-Month]),
VAR Actuals =
    IF (
        MAX ( SalesReport[Date Sold] ) = LastActualSaleDate,
        SalesReport[zEstimate IRCV],
        SalesReport[zAct. IRCV]
    )
VAR Future =
    CALCULATE (
        SUM ( 'Sales Projection 2023'[IRCV] ),
        FILTER ( 'a.Date Table', 'a.Date Table'[Date] > LastActualSaleDate )
    )

RETURN
   Future + Actuals
)

[rbriga]

Whenever indivisual values are correct and the aggragation is wrong, try an itirative function such as SUMX.

Try this (after adjustment):

zCombined Actual and Forecast2 = 
VAR LastActualSaleDate =
    CALCULATE ( MAX ( SalesReport[Date Sold] ), REMOVEFILTERS () )
SUMX(
	VALUES('a.Date Table'[Year-Month]),
	SWITCH(
		TRUE(),
		SalesReport[Date Sold]  < LastActualSaleDate, [zAct. IRCV],
		SesReport[Date Sold]  = LastActualSaleDate, SalesReport[zEstimate IRCV],
		SalesReport[Date Sold]  > LastActualSaleDate, SUM ( 'Sales Projection 2023'[IRCV] ),
		BLANK()
		)
	)

[raphazzz]

It didn't work, when I use SUMX is now changing the values of the rows too. 

[raphazzz]

up