Calculating submissions between 2 dates

[Kyle-92]

Hi

In my organisation we are required to submit a document everyday. So I am trying to create a dynamic measure where you can select the date range and see the number of submissions per employee that you should expect.

For example if the date range on the slicer is 1 October to 7 October you would expect 7 submissions.

The challenge I am facing is that an employee could be hired/fired within this period so you would not expect 7 submissions.

For example if the person was hired on the 6th of October you would only expect 2 submissions.

I have a date table and an employee register table (where there is a unique employee code column, a hiring date column and a leaving date column (which is left blank if the employee remains in the business)).

Could you provide some assistance on the dax one would use to calculate this?

Thank you!

[jdbuchanan71]

Hello @Kyle-92 

Give something like this a try.

Submissions = 
VAR _First = FIRSTDATE ( Dates[Date] )
VAR _Last = LASTDATE( Dates[Date] )
VAR _EFirst = MIN ( 'employee register'[hiring date] )
VAR _ELast = MIN ( 'employee register'[leaving date] )
VAR _Start = MAX ( _First, _EFirst )
VAR _End = IF ( ISBLANK ( _ELast ), _Last, MIN ( _Last, _ELast ) )
VAR Result = DATEDIFF( _Start, _End, DAY ) +1
RETURN Result

I was able to get the results I expected based on what your description.

I have attached my sample file for you to look at.

[Kyle-92]

Hi @jdbuchanan71 

Thank you so much!

Can I add one more complication as I thought this would be easy to resolve. 

Employees have managers who are responsible for ensuring the submissions are made. So in your example file employee A,B,C,D will report to Manager 1 and employee E,F,G will report to Manager 2 etc.

In this instance I need to add up the number of submissions expected for each manager (so Manager 1 would be 4 submissions per week) (but this obviously needs to take into consideration hiring and leaving dates of employees)

I don't know if you would be able to assist me on this? In addition will the negative submission values you have in your file influence this?

Really appreciate your help!!

[jdbuchanan71]

@Kyle-92 

Sorry I missed the negative numbers, that is easy to fix like so.

Submissions = 
VAR _First = FIRSTDATE ( Dates[Date] )
VAR _Last = LASTDATE( Dates[Date])
VAR _EFirst = MIN ( 'employee register'[hiring date] )
VAR _ELast = MIN ( 'employee register'[leaving date] )
VAR _Start = MAX ( _First, _EFirst )
VAR _End = IF ( ISBLANK ( _ELast ), _Last, MIN ( _Last, _ELast ) )
VAR _Check = _Start & " | " & _End & " | " & DATEDIFF( _Start, _End, DAY ) +1
VAR Result = DATEDIFF( _Start, _End, DAY ) +1
RETURN IF ( Result < 0, 0, Result )

Would the manager submissions be just the count of employees active in the report pertiod or would it be the sum of all the managers employess submissions?

[Kyle-92]

Hi @jdbuchanan71 

It would be the sum. Essentially what this would allow me to do is take the number of actual submissions over the period (which I already have in a factstable) divided by the number of expected submissions (which is what I need assistance with).

Just for completeness: an example would be:

Manager 1 has Employee A,B,C,D and therefore between a slicer selection of 1st October to 7th October is expected to have 28 submissions (7*4), however,per my factstable he only has 14.

My report can then show a bar graph with the percentage actually submitted per Manager which I would then be able to drilldown on to see which employees are contributing negatively to his expected submissions (as Employee A may have submitted all the documents but Employee B submitted only 10%).

Thank you

[jdbuchanan71 · Accepted solution]

We can change the measure to a SUMX that way the individual counts calculate correctly at the manager level.

Submissions = 
SUMX ( 
    VALUES( 'employee register'[Employee ID] ),
    CALCULATE(
        VAR _First = FIRSTDATE ( Dates[Date] )
        VAR _Last = LASTDATE( Dates[Date])
        VAR _EFirst = MIN ( 'employee register'[hiring date] )
        VAR _ELast = MIN ( 'employee register'[leaving date] )
        VAR _Start = MAX ( _First, _EFirst )
        VAR _End = IF ( ISBLANK ( _ELast ), _Last, MIN ( _Last, _ELast ) )
        VAR Result = MAX( 0, DATEDIFF( _Start, _End, DAY ) +1 )
        RETURN Result
    )
)

[Kyle-92]

Hi @jdbuchanan71

This worked perfectly thanks so much for your help!