Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous,

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

8 REPLIES 8
Abhi_Mhamal
Frequent Visitor

is anyone do have a solution do this problem, even i ma facing the same issue.

wrote DAX last week not working today.

financial_year = IF(MONTH(values(hoursworked[DATE_TO]))>3,YEAR(values(hoursworked[DATE_TO])&"-"&YEAR(hoursworked[DATE_TO])+1,YEAR(hoursworked[DATE_TO])-1&"-"&YEAR(hoursworked[DATE_TO]))

Anonymous
Not applicable

Did in the query screen with a custom column 

 

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

Hi @Anonymous,

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

Anonymous
Not applicable

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.

 

 

Anonymous
Not applicable

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

 

Hi @Anonymous,

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.