Forum Discussion
Count between start and end date
Hi joshcomputer1 ,
Your measure is correct however your datetable needs to be disconnected from your Test Table.
Since you have a relationship on the Start Date your data is filtered by the start date only so give you 6 as a result and not the expect outcome.
Check the image below and PBIX file attach:
Regards,
MFelix
Felix, I'm facing a similar challenge on this subject and came across this post.
I'm trying to use a direct query model to report historical monthly count of individuals between their respective start and end dates. For example, in February, an individual with Start_Date Jan 20 and End_Date Mar 20 counts as 1.
Fact_Table:
Person_ID | Start_Date | End_Date
Date_Table:
Date Dt | Date_ID | Many other columns
Trying to build the measure that you included above doesn't allow me to reference the Dim_Date table in the filter section. It allows me to pull Start and End date into the calculate statement, but not the Date Dt column from the Date Table. Anonymous seemed to have a similar issue. Do you have an idea on how to implement the Date >= Start Date and Date <= End Date?