DAX And PQ

[Anonymous]

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.

[Anonymous]

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

`

[aj1973]

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.

[Anonymous]

A quick question as the start date column has values like ASAP, Not filled when i change the format its coming as error. Or do u recon just change the format and see where it take to?

[aj1973]

You can't change the format of the Start date column to Date. the columns conatins text format like ASAP ! . I don't know the business logic coming from you Excel to really answer your question, all I can tell is that you need to work on your excel file and remove all calculated columns from it in order not have suprises when publishing to the service.

[Anonymous]

Hi I really appreciate your effort and thank you ever so much for helping, Yeah, I have been moved in recently to build dashboards and the source what they have been using is formula based and cannot make much of a change in it. In this case any other suggestion? Cos the whole data set is depending on formulas of each columns. Other columns are working fine as of now but the today and dates creating havoc.

[aj1973]

Hi @Anonymous 

Better use PQ.

Add column, then select column to create Month name column

Then add another according to your Vaccancy status

[Anonymous]

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])))

[aj1973]

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

[Anonymous]

Hi Aj

 Its a live excel which gets updated every minute so I am attaching a copy of the sheet with just the relevant details pertaining to this issue. If you see towards the end of the sheet there would be a column called Month and thats what the issue is all about. Kindly look into it and let me know, I do not have the option to attach the file could you help on that too pls?.

[aj1973]

Use an onlie service to upload the file, like OneDrive, Google Drive, Dropbox....