How to filter count by another date filter

[mdelasheras]

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!

[MFelix]

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

[mdelasheras]

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??

[MFelix]

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

[mdelasheras]

Hi @MFelix,

I need to count only those DONE jobs that had the TO_DO date between the filter dates. Let's see an example:

FILTER: 01/01/2018-31/03/2018

TO_DO	DONE
11/11/2017	01/02/2018 <-- doesn't count
12/12/2017	01/02/2018 <-- doesn't count
05/03/2018	08/03/2018
03/03/2018	04/04/2018
04/04/2018	04/04/2018

My output should be: 2-1 but now I'm getting 2-3 as result. Only those records remarked should be taken into account.

Thanks!