Count running total between marked dates

[Saap]

Hi all,

In my table I have following columns: [ContractStart], [ContractStop], [Client], [Employee] and, [ContractDuration].
I also have 3 measures: [ACTIVE] - marks the contract active in a given period using disconnected time table, [INFLUX] - marks the beginning of first contract or the first contract after 90 days break, [OUTFLUX] - marks the last contract or the last contract before 90 days break.

My table contains the data of many employees which work for many clients on many contracts.

My goal is to count the running total of contracts duration between the [INFLUX] and [OUTFLUX].

Below is the sample data filtered for one employee with desired outcome in [RollingTotal] column.

ContractStart	ContractStop	ACTIVE	INFLUX	OUTFLUX	ContractDuration	Client	RollingTotal
01.01.2017	31.01.2017	1	1		31	AAA	31
01.02.2017	28.02.2017	1			28	AAA	59
01.03.2017	31.03.2017	1			31	AAA	90
01.04.2017	30.04.2017	1			30	AAA	120
01.05.2017	31.05.2017	1			31	AAA	151
01.06.2017	13.06.2017	1		1	13	AAA	164
04.09.2018	30.09.2018	1	1		27	AAA	27
01.10.2018	31.10.2018	1			31	AAA	58
01.11.2018	30.11.2018	1			30	AAA	88
01.12.2018	31.12.2018	1			31	AAA	119
01.01.2019	31.01.2019	1			31	AAA	150
01.02.2019	27.02.2019	1		1	27	AAA	177
07.09.2020	30.09.2020	1	1		24	BBB	24
01.10.2020	31.10.2020	1			31	BBB	55
01.11.2020	30.11.2020	1			30	BBB	85
01.12.2020	31.01.2021	1			62	BBB	147
01.02.2021	28.02.2021	1			28	BBB	175
01.03.2021	13.03.2021	1			13	BBB	188
14.03.2021	14.03.2021	1		1	1	BBB	189
14.07.2021	16.07.2021	1	1		3	AAA	3
17.07.2021	31.07.2021	1			15	AAA	18
01.08.2021	31.08.2021	1			31	AAA	49
01.09.2021	30.09.2021	1			30	AAA	79
01.10.2021	31.12.2021	1			92	AAA	171
01.02.2022	17.02.2022	1			17	AAA	188
18.02.2022	28.02.2022	1			11	AAA	199
01.03.2022	31.03.2022	1			31	AAA	230
01.04.2022	30.04.2022	1			30	AAA	260
01.05.2022	11.05.2022	1			11	AAA	271
12.05.2022	31.05.2022	1			20	AAA	291
01.06.2022	30.06.2022	1		1	30	AAA	321

As you can see, this employee worked for two clients over the years with some breaks between the contracts. Sometimes breaks may occur when he works for one clients, sometimes when he changes to other client. I would like to count the running total of the work duration for each of those periods.

I need to calculate this, because later I want to count the average work duration of all the employees for a given period.

I've managed to count something like this:

Measure for rolling total is:
ROLLING TOTAL =
IF([ACTIVE]=BLANK(),BLANK(),
CALCULATE(SUM(CONTRACTS[ContractDuration]), FILTER(CONTRACTS, CONTRACTS[ContractStart]>=(CONTRACTS[Date of influx fixed]) && CONTRACTS[ContractStop]<=MAX('CALENDAR'[Date]))))

The CONTRACTS[Date of influx fixed] is a calculated column where I calculated the date of influx for every employee and every client.

The problem is I don't know how to make it calculate the rolling total from the beginning of each period between breaks.

[Anonymous · Accepted solution]

Hi @Saap ,

You could create a group based on [OUTFLUX].

M_group = CALCULATE(MIN('Table'[ContractStart]),FILTER(ALL('Table'),[M_OUTFLUX]=1&&'Table'[ContractStart]>=SELECTEDVALUE('Table'[ContractStart])))

Then calculate the running total based on the group.

Measure = CALCULATE(SUM('Table'[ContractDuration]),FILTER(ALLSELECTED('Table'),[M_group]=MAXX('Table',[M_group])&&'Table'[ContractStart]<=SELECTEDVALUE('Table'[ContractStart])))

Pbix as attached.

Best Regards,

Jay

[Saap]

Hi @Anonymous 

Thank you for your solution.
It works perfectly in your pbix file but I have problems implementing it in my report.
It seems that these measures are very performance heavy and the visual couldn't load:

It looks like it can't handle bigger amounts of data (my fact table contains about 200k rows).

Fortunately I found other solution. I just had to put the column CONTRACTS[Date of influx fixed] (I counted it earlier, you can see the code in my previous post) into the matrix's rows and then my measure counted the rolling total correctly.

I am not sure why it works that way. Maybe you could explain it to me?

Anyway thank you. I will accept your post as a solution 🙂