How to calculate the sum of dates between two peridos given by a table

[JoseLuisAGZZ]

Hi yall!

So im facing this:

HR department want to know how much the recruiters recruit in an specific date range:

So the tables are two, the first one goes like this:

RecruiterID      startDate            FinishDate       ExpectedInterviews
01                    01/01/2022         01/05/2022       5

02                    01/02/2022         01/07/2022       7

01                     02/01/2022         02/08/2022      6

and then my fact table is something like this:

RecruiterID      recruitDate     interviewID

01                     01/05/2022     001

01                     01/07/2022     002
02                     02/08/2022      115

So we want to know how much the recruiters have recruited in the date range we expected and defined in the first table

Any suggestion would be really apreciated

Best regards

[v-xiaotang · Accepted solution]

Hi @JoseLuisAGZZ 

You can try this measure, it returns the count of real interviews.

RealInterviewsDone =
CALCULATE (
    COUNTROWS ( 'Fact' ),
    FILTER (
        'Fact',
        'Fact'[recruitDate] <= MIN ( 'Table'[FinishDate] )
            && 'Fact'[recruitDate] >= MIN ( 'Table'[startDate] )
            && 'Fact'[RecruiterID] = MIN ( 'Table'[RecruiterID] )
    )
)

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @JoseLuisAGZZ 

Can you please share/add a sample of your expected result?

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

FIFA World Cup - Medal Records 

[JoseLuisAGZZ]

hi @VahidDM 

Sure, is the same first table with an extra column that returns the count of the interviews by recruiter and data range

RecruiterID      startDate            FinishDate       ExpectedInterviews   RealInterviewsDone
01                    01/01/2022         01/05/2022       5                                   2

02                    01/02/2022         01/07/2022       7                                   5

01                     02/01/2022         02/08/2022      6                                   2

Thanks in advance!

[VahidDM]

I can understand why RealInterviewsDone  for RecruiterID:1 is 2 but why it's 5 for the RecruiterID:2?

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[JoseLuisAGZZ]

In the first table is the metrics that we are giving to each of the recruiters,

so in the first range for the recruiter ID:01  from 1/01/2022    to  01/05/2022 we expect him to perform the 5 interviews we are asking him and so on for the others recruiters and date range.
So we want to calculate or count, the number of the real interviews that he uploaded to our facts table and place that number in a column so we can calculate the real vs the expected