topic Re: Need to count the number rows between two date in DAX Commands and Tips

Need to count the number rows between two date

leekohr — Fri, 01 Dec 2023 03:55:50 GMT

I would like to build table that counts the number of records between to dates.

I have data below call Data and date table called Calendar. My Data table looks like this.

I have date table has one column with all the dates from my data colunm.

When I use the following measure, I return only the date that are equal to the start date. When there is a relationship between Data[Start Date] and Calendar'[Date]. We I remove the relationship it works as expected. My question is why?

OpenItems =
VAR DateX = MAX('Calendar'[Date])
Return
CALCULATE(COUNTROWS(Data),Data[Start Date]<=DateX)

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

Re: Need to count the number rows between two date

FreemanZ — Fri, 01 Dec 2023 06:12:25 GMT

how is your measure visualized or what filter context do you have?

it could be that you are using the calendar[date] to get a date range and a date point in the same time, so only the the date point meets such requirement.

Re: Need to count the number rows between two date

leekohr — Fri, 01 Dec 2023 11:31:12 GMT

I add the date field to the X-Axis and the measure to Y-axis to create the following chart.

Re: Need to count the number rows between two date

Anonymous — Mon, 04 Dec 2023 07:48:53 GMT

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.