Conditional column/measure based on date

[Chorizo]

Hi all, 

I have a table with daily performance and estimate data as shown below: 

Date		Actual		Short Term Estimate		Long Term Estimate
01/06/21		0.2		0.3		0.33
02/06/21		0.3		0.3		0.33
03/06/21		0.3		0.3		0.33
04/06/21		0.2		0.3		0.33
05/06/21		0.2		0.3		0.33

... etc. 

Table1. Initial Table.

And I need display the data in below format. 

Date		Target		Actuals/Estimate		Source
Jan-21		10		8		Actual
Feb-21		10		9		Actual
Mar-21		10		10		Actual
Apr-21		10		11		Actual
May-21		10		7		Actual
Jun-21		10		10		Actual + Short Term  Estimate
Jul-21		10		9		Short Term Estimate
Aug-21		10		9		Short Term Estimate
Sep-21		10		9		Short Term Estimate
Oct-21		10		9		Long Term Estimate
Nov-21		10		9		Long Term Estimate
Dec-21		10		9		Long Term Estimate

Table2. Final Table

I also created a 'Calendar' Table with column in MMM-YY format. I have created a measure SelectedDate  = SELECTEDVALUE('Calendar'[Date]). that is filtered by slicers on the report page. 

I need to create a measure called "Actuals/Estimate" that will dynamically calculate based on these conditions:

1. If the date in table2 is <= SelectedDate then  display Actuals Value. 

2. For the dates between SelectedDate+1 and End Of Month date, Use Short Term Estimate

3. For 3 months after current months use Short Term Estimate

4. For the rest of the months, use Long Term Estimate. 

5. When a selected date is September, use only Short term estimate for dates between SelectedDate+1 and End Of Year.

Knowing all these conditions, I have tried to create a measure, but I could not figure it out. I could not build logic that allows to compare dates between Selected date (not static, linked to date slicer)  and the dates in Table 2 to define which values I can use (Actuals or Estimates).

Any advice or solution you can propose?

Thanks, 

Nurbek

[amitchandak]

@Chorizo , Based on what I got, Try like

Measure =
var _max = maxx(allselected('Calendar'), 'Calendar'[Date])
return
Switch( true() ,
month(_max) = 9 , Table2[Date] > _max && Table2[Date] <= date(Year(_max),12,31) , [ Short term estimate]
Table2[Date] <= _max , [Actual Value] ,
Table2[Date] > _max && Table2[Date] <= eomonth(_max,0),[Short Term Estimate] ,
Table2[Date] > eomonth(_max,0) && Table2[Date] <= eomonth(_max,3) ,[Short Term Estimate] ,
[Long Term Estimate]
)