Forum Discussion
Help with If Function in Measure
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?
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])
4 Replies
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.
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.
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])
