Hi
I'm trying to determine the running total of [hours/week] displayed by month/year. The basic algorithm is
RunningTotal = sum hours/week ( joined <= current and exit date is blank) + sum hours/week joined<= current period exit date > current period
I have a date dimension table with active relationship on joined, and inactive on exit.
I wasn't sure if this could be done in one or to measures
Here is some sample data
| Staff ID | Name | Joined | Exit Date | | BU | Hours Week |
| 56894 | Zac | 5/05/2018 | | Part Time | Sales | 40 |
| 12454 | Jack | 3/06/2018 | | Full Time | Sales | 33.75 |
| 12071 | Jorda | 7/08/2018 | | Full Time | Sales | 22.5 |
| 45679 | Bee | 23/08/2018 | | Part Time | Sales | 10 |
| 12066 | Jane | 14/09/2019 | | Part Time | Sales | 30 |
| 12070 | Louise | 4/10/2019 | | Part Time | Sales | 28 |
| 12079 | Emme | 5/10/2019 | | Full Time | Sales | 35 |
| 12134 | Colin | 6/10/2019 | | Contractor Full Time | Sales | 35 |
| 12130 | Julia | 6/11/2019 | 23/11/2019 | Part Time | Sales | 30 |
| 12150 | Sophie | 7/11/2019 | | Full Time | Sales | 35 |
| 12147 | Megan | 8/11/2019 | 3/03/2020 | Full Time | Sales | 35 |
| 12169 | Rose | 9/11/2019 | | Part Time | Sales | 33.75 |
| 12180 | Abby | 10/11/2019 | | Part Time | Sales | 22.5 |
and here is the expected outcome;
| Expected Outcome | |
| | | Total Hours |
| May | 2019 | 40.00 |
| June | 2019 | 73.75 |
| July | 2019 | 73.75 |
| Aug | 2019 | 106.25 |
| Sept | 2019 | 136.25 |
| Oct | 2019 | 234.75 |
| Nov | 2019 | 360.5 |
Staff Id of 12130 is excluded because they joined and left in the same month.
I can obtain the running total for the first condition (joined <= curr period and exit is null)
_measFTE_active = calculate(
sum('hrd-1'[Hours/Week])
,ISBLANK('hrd-1'[Exit])
,FILTER(all('Calendar-Join')
,'Calendar-Join'[Date] < max('Calendar-Join'[Date]
)
))
and the second conditon is not quite right (as it needs to make sure that joined <= max(date) of the exit period)
var inactive_val = CALCULATE(
SUM('hrd-1'[Hours/Week])
,USERELATIONSHIP('hrd-1'[Last Day of Duty],'calendar-join'[date])
,FILTER(ALLSELECTED('Calendar-Join'[Date]),'Calendar-Join'[Date] > MAX('Calendar-Join'[Date] )
)
)
return if(isblank(inactive_val),0,inactive_val)
On a side note, I've caculated the Running total on Head count with the same conditions, but haven't been able to convert it to a sum.
Here is the measure
CALCULATE(
COUNTx(
FILTER(
'hrd-1'
,'hrd-1'[Joined]<=max('Calendar-Join'[Date]) && (ISBLANK('hrd-1'[Exit]) || 'hrd-1'[Exit]>max('Calendar-Join'[Date]))
)
,('hrd-1'[Staff ID])
)
,CROSSFILTER('hrd-1'[Joined],'Calendar-Join'[Date],None))
If anyone could help it would be appreciated.
