Forum Discussion

charlessutton's avatar
charlessutton
Frequent Visitor
5 years ago
Solved

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. ...
Need Help
  • MattAllington's avatar
    MattAllington
    5 years ago

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

charlessutton's avatar
charlessutton
Frequent Visitor
5 years ago

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's avatar
v-kelly-msft
Community Support
5 years ago

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!