Measure (CALCULATE) - two fact tables

Question

Hi DAX Experts around the world,

I hope you can help me with below problem. In short:

• there are two fact tables: Table1 and Table2 , not connected between each other and cannot be appended.
• they have both relation to Calendar table
• I need to create a measure which will return some part from Table1 and a part form Table2, as described on the picture.
• When a Nov-21 is selected on the slicer, measure should return Actuals for first 10 months of the year with 100 each month (from Table1) and remaining 2 months with Forecast values from with 200 each month (from Table2).

Is it possible at all as I am out of ideas ?

Thank you in advance. Max

Answer 1

You can create a copy of the calendar table(Calender (2)), which can be used to create a slicer.

With the help of this table, you can create a measure to calculate the value based on selected date like this:

Amount Measure = 
VAR _selected_date = MAX('Calender (2)'[Date])
return SUMX(SUMMARIZE(Calender,Calender[Date]),IF('Calender'[Date]<_selected_date,SUMX(FILTER(Table1,Table1[Version]="Actual"),[Amount]),SUM(Table2[Amount])))