cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chorizo
Frequent Visitor

Conditional column/measure based on date

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

 

1 REPLY 1
amitchandak
Super User
Super User

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors