Not being able to generate a total value from a measure used in a matrices rows

[adkallday]

Hello,

First time post. I am working on a forecasting matrix and have already put quite a few calculations into the matrix. The next request I have been given was to take the values produceds for each Forecasted Monthly Rev and total them to get a complete furture forecast, but I cannot seem to figure it out. Below is my DAX for creating Forecasted Monthly Rev and an image of the matrix where it is used. My goal is to find the sum of all the forecasted monthly revs and put it into a card to display as a single total forecast value. 

Forecasted Monthly Rev =

VAR _PMACRR = [PrevMonACRR]

VAR _FCR = [Forecasted Cumulative Rev]

VAR _PMFCR = [PrevMonFCR]

VAR _ACRR = [Actual Cumulative Rev Recognized]

VAR _Result =

    IF (

        SELECTEDVALUE('Date Table'[Month Offset]) = 0 ,

        SWITCH (

            TRUE (),

            _FCR <= _ACRR, BLANK (),

            _FCR > _ACRR, _FCR - MAX(_PMFCR, _ACRR)

        ),

        IF (

            SWITCH (

                TRUE (),

                _FCR <= _PMACRR, BLANK (),

                _FCR > _PMACRR, _FCR - MAX ( _ACRR, _PMFCR )

            ) < 0,

            BLANK (),

            SWITCH (

                TRUE (),

                _FCR <= _PMACRR, BLANK (),

                _FCR > _PMACRR, _FCR - MAX ( _ACRR, _PMFCR )

            )

        )

    )

RETURN

_Result

[adkallday]

@DataInsights thank you for your response, I am still returning a blank. When I define with reference and evaluate I get more blanks for the previous measure used inside of this dax. Open to suggestions on how to fix previous measure. 

[DataInsights]

@adkallday,

Would you be able to provide a link to a sample pbix? You can use one of the file services like OneDrive.

[DataInsights]

@adkallday,

Try this measure. It uses ADDCOLUMNS to create a temporary column and then SUMX sums it. If your date table has a [Year Month] column, you can replace the SUMMARIZE expression with VALUES ( 'Date Table'[Year Month]).

Forecasted Monthly Rev =
VAR _ForecastTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Date Table', 'Date Table'[Year], 'Date Table'[Month] ),
        "@Forecast",
            VAR _PMACRR = [PrevMonACRR]
            VAR _FCR = [Forecasted Cumulative Rev]
            VAR _PMFCR = [PrevMonFCR]
            VAR _ACRR = [Actual Cumulative Rev Recognized]
            RETURN
                IF (
                    SELECTEDVALUE ( 'Date Table'[Month Offset] ) = 0,
                    SWITCH (
                        TRUE,
                        _FCR <= _ACRR, BLANK (),
                        _FCR > _ACRR, _FCR - MAX ( _PMFCR, _ACRR )
                    ),
                    IF (
                        SWITCH (
                            TRUE,
                            _FCR <= _PMACRR, BLANK (),
                            _FCR > _PMACRR, _FCR - MAX ( _ACRR, _PMFCR )
                        ) < 0,
                        BLANK (),
                        SWITCH (
                            TRUE,
                            _FCR <= _PMACRR, BLANK (),
                            _FCR > _PMACRR, _FCR - MAX ( _ACRR, _PMFCR )
                        )
                    )
                )
    )
VAR _Result =
    SUMX ( _ForecastTable, [@Forecast] )
RETURN
    _Result