Show sum of actuals when selected dates less than Max order date otherwise show forecast

[HongQiu]

I have date hierarchy filters in dashboard: Year filter, Quarter filter, Month filter. the max actual sales date is Feb 2022.  how to show the current actual + forecast if user selects Quarter 1( which including 2 months actuals and 1 month forecast ) ?
Or maybe user select different month, like Jan, Feb, April, Sep. I would like my measure results show sum of actuals from Jan and Feb + sum of forecast from April and Sep.

Thank you so much.

[HongQiu]

Could anyone please share how to use a similar function like selectedvalue to achieve this? selectedvalue only can return one date, what I would like to do is let user select multiple dates. I do have date table that connect the actuals and forecast, I dont want to append these two tables is because actuals and forecast show different levels data, with more than 1 year data, the file will be very big... thank you. 

[HongQiu · Accepted solution]

I have managed to do this by using this dax

meansurename = sumx(calendar, if(posting date<=dataRefreshingDate, [actuals],[forecast]))

[Ashish_Mathur]

Hi,

Actual and Forecast should appear in 1 Fact table.  Create a Calendar Table and build a relationship from the Date column of the combined Fact Table and the Date column of the Calendar Table.  To your visual, drag Year, Month name from the Calendar Table.

[HongQiu]

yes, I agree the best case is to have a good data model. but currently I only have actuals and forecast(higher category level) in different file. that would be great if you have some tips on dax. thanks

[Ashish_Mathur]

You can still append the 2 tables.