cancel
Showing results for
Did you mean:
Regular Visitor

## DAX can't find columns for dates

Hi,

I've been successfully using these sorts of DAX formulas for current month or week:

Current Month = if(MONTH(NOW())=[MonthOfYear],"yes","no")

IsCurrentMonth = IF (YEAR ( 'Invoked Function'[Date] ) = YEAR ( TODAY () )
&& MONTH ( 'Invoked Function'[Date] ) = MONTH ( TODAY () ),
"Yes",
"No"
)

Yesterday I found that it can't see the fields I want it to in the tables.  I get messages like this:

I've not done anything differently from a report I made earlier this week, so I'm confused why it can't seem to see the columns anymore.  The only difference may have been I updated to the latest version of the desktop.  Anyone experienced this before?

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: DAX can't find columns for dates

Hi @MunroA7,

You use the formula to create Current Month and IsCurrentMonth measures, and get the error message right? If it is, the cause is we are able to use IF() by itself in measure. But if I nest VALUES() inside of the IF(), it will work if the date column is single. For more details, please review this article.

I try to reproduce the screenshot as follows.

First, I create calculated column using the given measure, it works fine and return expected result.

While I get the same error message when I create measure using same formula.

Finally, I add the VALUES() function, it works fine. Please review the following formula.

IsCurrentMonth1 = IF ((YEAR(VALUES( Table2[Date] )) = YEAR ( TODAY () )
&& MONTH ( VALUES(Table2[Date] )) = MONTH ( TODAY () )),
"Yes",
"No"
)

If you have any question, please let me know.

Best Regards,
Angelia

7 REPLIES 7
Regular Visitor

## Re: DAX can't find columns for dates

Did in the query screen with a custom column

= Table.AddColumn(#"Changed Type", "Custom", each Date.IsInCurrentMonth([Date]))

Super Contributor

## Re: DAX can't find columns for dates

Hi @MunroA7,

You use the formula to create Current Month and IsCurrentMonth measures, and get the error message right? If it is, the cause is we are able to use IF() by itself in measure. But if I nest VALUES() inside of the IF(), it will work if the date column is single. For more details, please review this article.

I try to reproduce the screenshot as follows.

First, I create calculated column using the given measure, it works fine and return expected result.

While I get the same error message when I create measure using same formula.

Finally, I add the VALUES() function, it works fine. Please review the following formula.

IsCurrentMonth1 = IF ((YEAR(VALUES( Table2[Date] )) = YEAR ( TODAY () )
&& MONTH ( VALUES(Table2[Date] )) = MONTH ( TODAY () )),
"Yes",
"No"
)

If you have any question, please let me know.

Best Regards,
Angelia

Regular Visitor

## Re: DAX can't find columns for dates

Thanks @v-huizhn-msft I'm just curious why I have to use VALUE now?  The simplist formula has stopped working now, such as Days Since Activity =

I'm wondering why I've got to change everything now and add VALUE.  I don't understand what is different.  It also means any solutions I find on Power BI have to be adapted so I can use them.  I'm curious to know why this is.

Regular Visitor

## Re: DAX can't find columns for dates

Actually nothing date-wise is working anymore  none of the date forumlas can see any date columns....I don't get it.

I've tried that with value it doesn't work either.  I think there is a problem....

Super Contributor

## Re: DAX can't find columns for dates

Hi @MunroA7,

Sorry for inconvenience, the IF function in unable to use in measure. If you want to use it in measure, you’d better use value function, but the prerequisite is the column has single value. Based on your description, the ‘Opportunity’[estimatedclosedate] doesn’t exist, please verify the column field exists and your DAX written is right.

Best Regards,
Angelia

Highlighted
Regular Visitor

## Re: DAX can't find columns for dates

Hi Angelia,

Yes, I've passed this to support and they can't get date columns to appear either so we shall see what happens.

Regular Visitor

## Re: DAX can't find columns for dates

Did this problem ever get fixed? Using Values() is not a solution, more of a work-around.

We are on August 2018 version of Power BI Report Server and Desktop for Report Server. I have a user who is having this same problem.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 209 members 2,367 guests
Recent signins: