Filtering a CALENDAR table to only include first of the month dates

[cheresier]

Consider a table defined with the following DAX:

FirstOfTheMonth = FILTER(CALENDAR(DATE(2000, 1, 1), DATE(2030, 12, 31)), DAY([Date] = 1))

Effectively, I am trying to have a table that contains all first-of-the-month dates in a given range. However, it seems that the filtering by DAY([Date])=1 is having no effect:

Interestingly, filtering by MONTH works. So, defining the table as FILTER(CALENDAR(DATE(2000, 1, 1), DATE(2030, 12, 31)), MONTH([Date] = 3)) would result in a table that only contains March dates in all the years between 2000 and 2030 (as expected).

What is it about filtering by DAY([Date])=1 that is not working?

Thanks!

[Fowmy · Accepted solution]

@cheresier 

You added the last bracket inside DAY

Try this

FirstOfTheMonth = FILTER(CALENDAR(DATE(2000, 1, 1), DATE(2001, 12, 31)), DAY([Date]) = 1)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[cheresier]

Well, that was embarassing. Thank you. 

Now I wish there was a way to delete this post since it clearly adds no value to the community and serves no other purpose than as a record of my stupidity 🙂

[MEEnergy]

You can use the Startofmonth function to get the first date of each month. Treat

FirstOfMonth ? STARTOFMONTH(DateTable[Date].[ Date])