Filter ignores the last day of the month that is 31st of the month

[hieptl]

Hi, I am trying to filter a data set based on the post_date and post_modified_date. What I did so far was

1. Create a calendar table fromt these two tables as you can see here 2. Put this into a filter for dates that should filter the visuals based on the selection of the month/year. So when I select fx March 2023 it should take the 1st March 2023 00:00:00 until 31st March 2023 00:00:00  

3. I created a measure that takes into account the start and end date of the selected filter as following 

4. I am comparing it to this simple measure countrows of the table with filters on - edited, March, 2023 as you can see here 

       

5. As you can see here however it ignores the 31st of the month where it stop counting which makes the report inaccurate. The first two columns are post_modified_date and the next 2 are post_date. 

My question is what causes the filter to ignore the 31st day and how can I solve this please? Thank you in advance for any help

[hieptl]

jonht75 you are a legend! Thank you 🙂

[johnt75 · Accepted solution]

The problem is that the columns are all datetime not date, so something that happened at e.g. 31/3/2023 13:45 is excluded from the results because it is after 31/3/2023 0:00.

If you don't need the time parts for any other calculations then the simplest thing is to change the types of the columns in both your fact table and date table to be date rather than datetime. For you fact table you will need to change it in both power query and in the modelling view.

If you need to keep the time part for some reason you could amend the definition of EndRange to be MAX('date_filter'[date]) + 1