Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
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]))
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.
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
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.
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....
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
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.
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |