Create measure merging forecast and year to date actuals

[floralobi]

Hi i am trying to create a measure where total for each fin month is used for records prior to the first day of the current month (01.06.17) with the total forecast per fin month used for future months (in this case June 17).

Forecast = CALCULATE(SUM('Excel Forecast kgs'[Kgs Collected]))

Actual= CALCULATE(sum('Database - Volumes'[Quantity Received]) )

Test Measure2 = (IF(max('Database - Volumes'[Collection Date.Date].[Date]) < [FIRSTDAYMTH],[Actual],[Forecast]))

But the value that comes back for the forecast of the final month is incorrect, i should be getting 962.7.

 

[Anonymous]

@floralobi,

Do you want to show 962.7 for Test Measure2 in June 2017? If so, could you please share sample data of Excel Forecast kgs table and Database – Volumes table? We also need to know what formula you use to create FIRSTDAYMTH.

Regards,
Lydia