Conditional column/measure based on date

Question

Hi all,&nbsp;&nbsp;I have a table with daily performance&nbsp;and estimate data as shown below:&nbsp;&nbsp;Date&nbsp;&nbsp;Actual&nbsp;&nbsp;Short Term Estimate&nbsp;&nbsp;Long Term Estimate01/06/21&nbsp;&nbsp;0.2&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3302/06/21&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3303/06/21&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3304/06/21&nbsp;&nbsp;0.2&nbsp;&nbsp;0.3&nbsp;&nbsp;0.3305/06/21&nbsp;&nbsp;0.2&nbsp;&nbsp;0.3&nbsp;&nbsp;0.33... etc.&nbsp;Table1. Initial Table.&nbsp;&nbsp;And I need display the data in below format.&nbsp;Date&nbsp;&nbsp;Target&nbsp;&nbsp;Actuals/Estimate&nbsp;&nbsp;SourceJan-21&nbsp;&nbsp;10&nbsp;&nbsp;8&nbsp;&nbsp;ActualFeb-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;ActualMar-21&nbsp;&nbsp;10&nbsp;&nbsp;10&nbsp;&nbsp;ActualApr-21&nbsp;&nbsp;10&nbsp;&nbsp;11&nbsp;&nbsp;ActualMay-21&nbsp;&nbsp;10&nbsp;&nbsp;7&nbsp;&nbsp;ActualJun-21&nbsp;&nbsp;10&nbsp;&nbsp;10&nbsp;&nbsp;Actual + Short Term&nbsp; EstimateJul-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;Short Term Estimate&nbsp;Aug-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;Short Term EstimateSep-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;Short Term EstimateOct-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;Long Term EstimateNov-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;Long Term EstimateDec-21&nbsp;&nbsp;10&nbsp;&nbsp;9&nbsp;&nbsp;Long Term EstimateTable2. Final Table&nbsp;I also created a 'Calendar' Table with column in MMM-YY format. I have created a measure SelectedDate&nbsp; = SELECTEDVALUE('Calendar'[Date]). that is filtered by slicers on the report page.&nbsp;I need to create a measure called "Actuals/Estimate" that will dynamically calculate based on these conditions:1. If the date in table2 is &lt;= SelectedDate then&nbsp; display Actuals Value.&nbsp;2. For the dates between SelectedDate+1 and End Of Month date, Use Short Term Estimate3. For 3 months after current months use Short Term Estimate4. For the rest of the months, use Long Term Estimate.&nbsp;5. When a selected date is September, use only Short term estimate for dates between SelectedDate+1 and End Of Year.&nbsp;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)&nbsp; and the dates in Table 2 to define which values I can use (Actuals or Estimates).Any advice or solution you can propose?&nbsp;Thanks,&nbsp;Nurbek&nbsp;

amitchandak · Answer

Chorizo , Based on what I got, Try like 
&nbsp;
Measure = var _max = maxx(allselected('Calendar'), 'Calendar'[Date]) return Switch( true() ,month(_max) = 9 , Table2[Date] &gt; _max &amp;&amp; Table2[Date] &lt;= date(Year(_max),12,31) , [ Short term estimate]Table2[Date] &lt;= _max , [Actual Value] , Table2[Date] &gt; _max &amp;&amp; Table2[Date] &lt;= eomonth(_max,0),[Short Term Estimate] ,Table2[Date] &gt; eomonth(_max,0) &amp;&amp; Table2[Date] &lt;= eomonth(_max,3) ,[Short Term Estimate] ,[Long Term Estimate])

Forum Discussion

Conditional column/measure based on date

1 Reply

Related content

Create measure to return value from column when condition met for two other columns

Measure two columns

DAX Measure Base on Column value

Measure to return rows based on column conditions

new measure based on column value

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

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