Forum Discussion

Kyle-92's avatar
Kyle-92
Frequent Visitor
6 years ago
Solved

Calculating submissions between 2 dates

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

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

     

     

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

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's avatar
    Kyle-92
    Frequent Visitor
    6 years ago

    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's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        Kyle-92
        Frequent Visitor
        6 years ago

        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