Measure take for ever to run

[BlueWhite3699]

Hi Experts

the following measure retuns back the correct return - no issue there. But when applied to a 16million rows of data for one fiscal year its just take forever to return a value back in the matrx table...

YTD Forecast (full year) = 
        VAR TEMPTABLE = SUMMARIZE(
			CALCULATETABLE(
				ALLSELECTED('Date'),
				DATESYTD(
					'Date'[Date],
					"31/03"
				)
			),
			'Date'[Month and Year],
			"@Forecast", [Forecast]
		)
		RETURN

			SUMX(
				TEMPTABLE,
				[@Forecast]
			)

How can this be modifed to run more fast and better.

See sample file

https://drive.google.com/file/d/1UzRnZMz2UZS9qhojxQVRjHWs1HnXj4Dr/view?usp=drive_link 

[BlueWhite3699 · Accepted solution]

YTD Forecast (full year) = 
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            VALUES ( 'Date'[_MonthYearSort] ),
            "@TotalForecast", [Forecast]
        ),
        [@TotalForecast]
    ),
    DATESYTD ( 
        'Date'[Date],
        "31/03"
    ) 
)

[BlueWhite3699]

See image as the correct end value 407839, what is the image above is not correct.

[rajendraongole1]

Hi @BlueWhite3699  - 

YTD Forecast (full year)_1 =

CALCULATE(

    [Forecast],  

    DATESYTD('Date'[Date], "31/03")  

)

check this and let us know.

[BlueWhite3699]

Hi - that formula is not going to work on 16million rows of data and based on the model. I have tried that and give the wrong answer

[rajendraongole1]

Hi @BlueWhite3699 - can you please check the below logic:

YTD Forecast (full year)_1 =

VAR YTDForecast =

    CALCULATE(

        [Forecast], -- Use SUM directly for performance

        DATESYTD('Date'[Date], "31/03"),

        REMOVEFILTERS('Date')

    )

RETURN YTDForecast

[BlueWhite3699]

Hi Expert - its still incorrect look at my value for Mar 25 407839. thats the correct answer and kindly look at your value