Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total using active and inactive relationship

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 IDNameJoinedExit Date BUHours Week
56894Zac5/05/2018 Part TimeSales40
12454Jack3/06/2018 Full TimeSales33.75
12071Jorda7/08/2018 Full TimeSales22.5
45679Bee23/08/2018 Part TimeSales10
12066Jane14/09/2019 Part TimeSales30
12070Louise4/10/2019 Part TimeSales28
12079Emme5/10/2019 Full TimeSales35
12134Colin6/10/2019 Contractor Full TimeSales35
12130Julia6/11/201923/11/2019Part TimeSales30
12150Sophie7/11/2019 Full TimeSales35
12147Megan8/11/20193/03/2020Full TimeSales35
12169Rose9/11/2019 Part TimeSales33.75
12180Abby10/11/2019 Part TimeSales22.5

 

and here is the expected outcome;

Expected Outcome 
  Total Hours
May201940.00
June201973.75
July201973.75
Aug2019106.25
Sept2019136.25
Oct2019234.75
Nov2019360.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.



2 Replies