Forum Discussion
Calculating submissions between 2 dates
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 ) )
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
Super UserWe 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
)
)