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

MattAllington's avatar
MattAllington
Community Champion
5 years ago

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'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. 

  • MattAllington's avatar
    MattAllington
    Community Champion
    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])

  • 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!