cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MunroA7 Regular Visitor
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:

CM 1.PNGCM 2.PNG

 

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
v-huizhn-msft Super Contributor
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.

1.png

 

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

2.png

 

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

View solution in original post

7 REPLIES 7
MunroA7 Regular Visitor
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]))

v-huizhn-msft Super Contributor
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.

1.png

 

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

2.png

 

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

View solution in original post

MunroA7 Regular Visitor
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 =
DATEDIFF('Leads Activities'[Activity Created].[Date], TODAY(), DAY).

 

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.

 

 

MunroA7 Regular Visitor
Regular Visitor

Re: DAX can't find columns for dates

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

 

 

ECD dAte.PNG

 

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

 

v-huizhn-msft Super Contributor
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
MunroA7 Regular Visitor
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.

 

Thank you for your suggestions.

slarkin Regular Visitor
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. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 209 members 2,367 guests
Please welcome our newest community members: