Forum Discussion
How to filter count by another date filter
Hi all,
I have a table like this one:
JOBS
TO_DO : date
DONE: date
I also have a FILTER by TO_DO date (start/end)
I want to show how many jobs had to be done and how many were done filtering by date.
Now I got a graph where I can see how many had to be done and how many have been done. The problem is I don't know how to filter the second date by the same range so some of them may be done out of range.
I will add an example to clarify:
FILTER: 01/01/2018-31/03/2018
| TO_DO | DONE |
| 01/02/2018 | 01/02/2018 |
| 02/02/2018 | |
| 05/03/2018 | 08/03/2018 |
| 03/03/2018 | 04/04/2018 |
| 04/04/2018 | 04/04/2018 |
Now my output would say 4 (TO_DO) and 3 (DONE). It should be 4-2 because the last March Job was done out of range.
Hope my explanation is clear enough. Any tip?
Thanks in advance!
4 Replies
Hi mdelasheras,
Taking into account that you are using two date columns you need to:
- Create a calendar table
- Make inactive relationships between calendar table and both date columns
- Create the following measures:
TO_DO_count = CALCULATE ( COUNT ( JOBS[TO_DO] ); USERELATIONSHIP ( 'Calendar'[Date]; JOBS[TO_DO] ) ) DONE_count = CALCULATE ( COUNT ( JOBS[TO_DO] ); USERELATIONSHIP ( 'Calendar'[Date]; JOBS[DONE] ) )- Add the measures the date column from the calendar table to the chart
- Make the slicer based on calendar table
Result is as below:
Regards,
MFelix
Thanks MFelix, very clear.
Just one more thing, I would need to show only which jobs where done of those that had to be done in that period. I mean that case shouldn't be taking into account (second bar):TO_DO: 12/12/2016
DONE: 01/01/2018
There is a simple way to force the second count to be a subset of the first??
Hi mdelasheras,
Not really sure what you want to have the done list is based on the filter dates so is within the subset.
can you explain a little better with examples.
Regards,
MFelix
