topic Re: How to change the day portion of the date to "1". in DAX Commands and Tips

How to change the day portion of the date to "1".

Anonymous — Mon, 08 Aug 2022 10:30:58 GMT

Hi Everyone,

I've a date where I've a range for 2 years and in this range I want to change all the days to 1st of that month. How can I do that I am working with directquery.

Thanks

Re: How to change the day portion of the date to "1".

Anonymous — Mon, 08 Aug 2022 10:32:17 GMT

tamerj1 amitchandak Jihwan_Kim SpartaBI johnt75

Re: How to change the day portion of the date to "1".

tamerj1 — Mon, 08 Aug 2022 10:33:19 GMT

Hi Anonymous
You can use

EOMONTH ( TableName[Date], -1 ) + 1

Re: How to change the day portion of the date to "1".

Anonymous — Mon, 08 Aug 2022 10:37:17 GMT

Hi tamerj1 ,

Thanks for responding.

I've checked "EOMONTH" but It does not work in directquery mode.

Re: How to change the day portion of the date to "1".

tamerj1 — Mon, 08 Aug 2022 10:38:55 GMT

Anonymous

Try

= DATE ( YEAR ( TableName[Date] ), MONTH ( TableName[Date] ), 1 )

Re: How to change the day portion of the date to "1".

Anonymous — Mon, 08 Aug 2022 10:41:38 GMT

No luck with this one as well.

Re: How to change the day portion of the date to "1".

selinaz — Thu, 11 Aug 2022 08:07:27 GMT

Hi Anonymous ，

The Direct query connection mode is very restrictive for DAX functions, in another word, most DAX statements may not be supported in this mode, hence your error above. The more recommended method for you is to write the relevant SQL statements directly when connecting to the data source, to present the data you need precisely to the desktop.

Best regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly .

Re: How to change the day portion of the date to "1".

tamerj1 — Thu, 11 Aug 2022 12:19:15 GMT

Anonymous
Have tryed creating it as a measure instead of a column? is this a valid option fro your case?