cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saap
Resolver II
Resolver II

Count running total between marked dates

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.

ContractStartContractStopACTIVEINFLUXOUTFLUXContractDurationClientRollingTotal
01.01.201731.01.201711 31AAA31
01.02.201728.02.20171  28AAA59
01.03.201731.03.20171  31AAA90
01.04.201730.04.20171  30AAA120
01.05.201731.05.20171  31AAA151
01.06.201713.06.20171 113AAA164
04.09.201830.09.201811 27AAA27
01.10.201831.10.20181  31AAA58
01.11.201830.11.20181  30AAA88
01.12.201831.12.20181  31AAA119
01.01.201931.01.20191  31AAA150
01.02.201927.02.20191 127AAA177
07.09.202030.09.202011 24BBB24
01.10.202031.10.20201  31BBB55
01.11.202030.11.20201  30BBB85
01.12.202031.01.20211  62BBB147
01.02.202128.02.20211  28BBB175
01.03.202113.03.20211  13BBB188
14.03.202114.03.20211 11BBB189
14.07.202116.07.202111 3AAA3
17.07.202131.07.20211  15AAA18
01.08.202131.08.20211  31AAA49
01.09.202130.09.20211  30AAA79
01.10.202131.12.20211  92AAA171
01.02.202217.02.20221  17AAA188
18.02.202228.02.20221  11AAA199
01.03.202231.03.20221  31AAA230
01.04.202230.04.20221  30AAA260
01.05.202211.05.20221  11AAA271
12.05.202231.05.20221  20AAA291
01.06.202230.06.20221 130AAA321

 

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:

Saap_0-1653309942488.png

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.

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

vjaywmsft_0-1653635639109.png

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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

vjaywmsft_0-1653635639109.png

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi @v-jayw-msft 

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:

Saap_0-1653642052263.png

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.

Saap_1-1653642383863.png

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 🙂

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors