Returning Wrong SUM Result From Another Table

[snayff]

Objective

I'm trying to return 1 of 2 values based on whether 1 of those values exists. e.g. give me A if A exists, else B. These values are held in separate tables. More specifically, I have forecast values by provider, source and date and have actuals as a list of transactions, meaning multiple rows per provider, per month from each source. Where we have actuals I want to use those, else use the forecast. 

Problem

The forecast returns correctly, but the returned "actual" value is wrong, and in fact so wrong that I can't tie it back to any combination of numbers. 

Details

Here are the actuals and forecast by month for a specific source and provider. I'd expect `Spend_ActualOrForecast` to be 204,405.54 in June (same as actuals, because we have actuals) and 199,999 in July (same as forecast, because no actuals).

The calculated column in question

Spend_ActualOrForecast = 
    // use actual if we have it, else us forecast
    
    VAR resource_provider = 
        factForecast[Resource Provider]

    VAR source = 
        factForecast[Source.Name]

    VAR journal_type = 
        IF(
            resource_provider = "Us",
            "G",
            "P"
        )

    VAR start_date = 
        DATE(
            YEAR(factForecast[Forecast Anchor Date]),
            MONTH(factForecast[Forecast Anchor Date]),
            01
        )
    
    VAR end_date = 
        EOMONTH(
            DATE(
                YEAR(factForecast[Forecast Anchor Date]),
                MONTH(factForecast[Forecast Anchor Date]),
                01
            ),
            0
        )

    VAR actual = 
        CALCULATE(
            SUM(factTransactions[Account]) * -1,
            factTransactions[Normalised Expense Group] = resource_provider,
            factTransactions[Source.Name] = source,
            factTransactions[Journal Type] = journal_type,
            factTransactions[Reporting Date] >= start_date,
            factTransactions[Reporting Date] <= end_date
        )

    RETURN
        IF(
            actual = 0,
            factForecast[Forecast Value],
            actual
        )            

Sample Data

factForecast

Source.Name	Resource Provider	Forecast Value	Forecast Anchor Date	Spend_ActualOrForecast
project1	ABC	£199,999	July 23
project1	ABC	£0	December 23
project1	ABC	£0	November 23
project1	ABC	£0	October 23
project1	ABC	£0	September 23
project1	ABC	£0	August 23
project1	ABC	£0	June 23
project1	ABC	£0	May 23
project1	ABC	£0	April 23
project1	ABC	£0	March 23
project1	ABC	£0	February 23
project1	ABC	£0	January 23
project1	Us	£0	December 23
project1	Us	£0	November 23
project1	Us	£0	October 23
project1	Us	£0	September 23
project1	Us	£0	August 23
project1	Us	£0	July 23
project1	Us	£0	June 23
project1	Us	£0	May 23
project1	Us	£0	April 23
project1	Us	£0	March 23
project1	Us	£0	February 23
project1	Us	£0	January 23

factTransactions

Source.Name	Amount	Journal Type	Reporting Date	Normalised Expense Group
project1	-£204,406	P	Jun 23	ABC
project1	£284	G	Feb 23	Us
project1	-£284	G	Feb 23	Us
project1	-£284	G	Feb 23	Us
project1	-£652	P	Mar 23	ABC

Any help would be greatly appreciated!

[snayff]

Any chance someone has any thoughts on this? 🤞