Help with If Function in Measure

[charlessutton]

I have two data tables and a calendar table. The data tables actual values by month and forecasted values by month. I would like to display the value of either actual or forecast based on the month. Previous months = actual current and future months = forecast. 

When I write the measure with an If function I cannot use the calendar table/columns in the expression. What am I missing? 

[MattAllington]

This is what I would do

1. Create a matrix visual and put calendar month on rows (ideally YYYY-MMM or similar). You can also nest year and month and expand the matrix. 

2. Write the actual measure and place in values

3. Write the forecast measure and place in values

4 write a test measure max(actual[date]) and place in values

See if you can work it out from there. Preparing your data in a visual so you can see what is going on is one of the most important habits you can follow when learning DAX. 

[charlessutton]

Thank you for the reply. That is essentially what I have done. I am having a problem with an if statement. It will not recognize my Calendar Table/Columns as part of the function. I am trying to write a measure like this 

if(month(calendar[date]) < month(today()), ActualValue, ForecastValue) 

this would logically populate the actual values in the previous months and forecast values in current/future months. 

[v-kelly-msft]

Hi  @charlessutton ,

Have you create relationships between data tables and date table?

It works fine here using below dax expression：

Measure = IF(MONTH(MAX('calendar table'[Date]))<MONTH(TODAY()),MAX('Actuals values'[value]),MAX('forecast values'[value]))

And you will see:

For my sample .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[MattAllington · Accepted solution]

I can't see how that will work over the year end. I recommend adding a calc column that determines if the date has passed, or is in the future. Eg

Status=IF(calendar[date]<today(),"past","future")

then write a measure

=IF(SELECTEDVALUE(Calendar[Status])="Past",[Actual],[Forecast])