Forum Discussion
DAX And PQ
Excel data set has a column "Start Date" and column " Vacancy status". There is a formula which is created in excel to extract "Name of the month" in the data set. which is causing an error while refreshing power bi dashboard as this formula contains "Today()" function. So I want to use the same concept to extract Month name in DAX or PQ. i am attaching the formula for reference "=IF(Vacancy type<>"Approved Vacancy","",IF(Start Date="ASAP",TEXT(TODAY(),"mmmm yy"),TEXT(Start date,"mmmm yy")))". Kinldy help.
10 Replies
Hi Anonymous
Better use PQ.
Add column, then select column to create Month name column
Then add another according to your Vaccancy status
Hi
I have tried everything I could used conditional columns, used multiple ways modified PQ formula with the help chatgpt but nothing seems to be working. I think the issue is that the column start date as values like "blanks", "Asap", and "dates". and the Today or now function is just not working.
Sharing one PQ example: (if [Vacancy type] <> "Approved Vacancy" then null else if [Start Date] = "ASAP" then Text.From(Date.Month(DateTime.LocalNow())) & " " & Text.From(Date.Year(DateTime.LocalNow())) else Text.From(Date.Month([Start Date])) & " " & Text.From(Date.Year([Start Date])))
In previous thread I told you that calculated columns in Excel must not be used in Power BI.
If you can send me a sample of your excel file and the desired outcome I will be better helpful
Would this be of any help? If you scroll towards your right there would be a month column and right next to it is the today function, I am also sharing the formula used in the month column for reference. =IF(A2<>"Approved Vacancy","",IF(G2="ASAP",TEXT(TODAY(),"mmmm yy"),TEXT(G2,"mmmm yy")))
Vacancy Type
Vacancy Contract Type Hours Approved Date Approved by Start Date Staff Member Notes Recruitment Need Talent Finder June start 0 Board Report Category Approved Week Number Summary Approved? Nursery Director Report Group Month 06/02/2024 Pending Vacancy NA Staff A 0 ASAP Replace X #N/A #REF! Pending Approval r #REF! Pending Vacancy NA Staff A 0 06/02/24 Replace X #N/A #REF! Pending Approval r #REF! Pending Vacancy NA Staff A 0 Replace X #N/A #REF! Pending Approval r #REF! Pending Vacancy NA Staff A 0 04/02/24 Replace X #N/A #REF! Pending Approval r #REF! Approved Vacancy NA Staff A Replace X ` your Start Date column is not Date format and can't be, that's why the M code couldn't find the date component in your code.
Can you please show me the out come I might look for a solution on my own. Thanks.
