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
Hi, MFelix
I've tried this solution when having csv of excel files as a dataset which seems to work fine. However when I'm working with an online database using direct query it doesn't. I'm trying to count the number of id's between the start date and end date, but with direct query it's only counting the id on the start date or not whatsoever.
Any ideas why this method doesn't work while working with online datasets?
I appreciate any input!
Hi Anonymous ,
If you are using direct query then the calculation needs to be different since you are not abble to create disconnected table.
Try the following code:
DateSlicer = CALCULATE(
COUNTROWS(contractid),
FILTER(ALL(Table1),
DateTable[Date] >= [startdate] &&
DateTable[Date] <= [Enddate])Hi MFelix ,
thanks for your quick response. Not sure what you mean though. With the COUNTROWS statement I have to refer to a table and not a column and I also can't refer to the date table in this measure.
Hope you can help me
Cheers,
MarcoHi Anonymous ,
You need to reference the table that you want to filter, the column is reference on the filter statement.
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?
