Need to count the number rows between two date

Hi leekohr ,

The behavior you're observing is likely due to the way DAX handles filter context in Power BI. When there is an active relationship between the Data table and the Calendar table, the filter context is automatically applied based on that relationship. This means that when you calculate the measure, it is only considering the dates that match exactly in both tables due to the relationship.

Here's a step-by-step action plan to resolve the issue:

1. Review Relationships:

- Ensure that the relationship between the Data table and the Calendar table is set up correctly. It should typically be a one-to-many relationship, with the Calendar table on the "one" side.

2. Modify the Measure:

- You may need to adjust your measure to ignore the filter context imposed by the relationship. You can do this by using the ALL() function or to REMOVEFILTERS() function the filter context from the Calendar table.

OpenItems = 
VAR DateX = MAX('Calendar'[Date])
Return
COUNTROWS(FILTER(ALL(Data),Data[Start Date]<=DateX && Data[End Date]>Datex))

Best Regards,

Neeko Tang

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