Conditional column/measure based on date

Chorizo — Fri, 18 Jun 2021 09:35:35 GMT

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

Re: Conditional column/measure based on date

amitchandak — Fri, 18 Jun 2021 05:41:13 GMT

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]
)

topic Conditional column/measure based on date in DAX Commands and Tips

Conditional column/measure based on date

Re: Conditional column/measure based on date