Forum Discussion
Count on table with filter on 2 date fields
PascalM ,
If you have a relationshp between those two tables, that won't give you any problem. And as you have given hardcoded value you don't need to use MIN or MAX.
If you want to change the date values dynamically when you apply filters create variables with SELCTEDVALUE and use in the measure where date =.
Regards,
Manikumar
Solution SagePascalM ,
In DAX also it is straight forward. Something like below
Calculate(COUNT(JobId) ,FILTER( table,MIN(table[StartDate]) >= ‘20200101’ AND MAX(table[EndDate) <= ‘20200630’)
assuming the same format for dates as you have given.
Regards,
Manikumar
Also, I don't think that in the formula you suggest, the MIN and MAX work as expected. MIN and MAX should only be applied on rows for the same JobId. I think that in your formula it looks at all JobIds. So first I need to transform my original Task table with only 1 row per JobId, and corresponding MIN and MAX dates. Then I need to count which of these Tasks fall within the specified date range.
- manikumar34
PascalM ,
If you have a relationshp between those two tables, that won't give you any problem. And as you have given hardcoded value you don't need to use MIN or MAX.
If you want to change the date values dynamically when you apply filters create variables with SELCTEDVALUE and use in the measure where date =.
Regards,
Manikumar