Cumulative Sum by Person by Week during the Year DAX

[Anonymous]

Hello, PBI Experts!

Could you, please, help me with such issue...

I have a table with Time Entries for a large number of users. This information also includes TimeEntryID, TaskID, Entry Date, Task Duration, Week Day, Week Number, Entry Year and Month and etc.

I want to calculate a cumulative sum of duration hours per user per week (ISO week) in order to find the day of the week where this sum for the user is > 40 and to say on which day of the week this overtime appears.

The dataset in big but just to show an example:

Time Entry ID	User ID	User Name	Entry Date	Entry Week Num	Entry Year	Entry Week Day	Duration
1	1	A	1/1/2018	1	2018	1	0.5
2	1	A	1/1/2018	1	2018	1	0.7
3	1	A	1/2/2018	1	2018	2	1.5
4	1	A	1/2/2018	1	2018	2	2.5
5	1	A	1/3/2018	1	2018	3	3.5
6	1	A	1/4/2018	1	2018	4	4.5
7	1	A	1/5/2018	1	2018	5	1
8	1	A	1/6/2018	1	2018	6	2
9	1	A	1/9/2018	2	2018	2	9.5
10	1	A	1/9/2018	2	2018	2	10.5
11	1	A	1/10/2018	2	2018	3	0.4
12	1	A	1/10/2018	2	2018	3	3
13	1	A	1/10/2018	2	2018	3	4
14	1	A	1/10/2018	2	2018	3	5
15	1	A	1/11/2018	2	2018	4	3
16	1	A	1/11/2018	2	2018	4	0.5
17	2	B	1/2/2018	1	2018	2	0.7
18	2	B	1/2/2018	1	2018	2	1.5
19	2	B	1/2/2018	1	2018	2	2.5
20	2	B	1/2/2018	1	2018	2	3.5
21	2	B	1/2/2018	1	2018	2	4.5
22	2	B	1/3/2018	1	2018	3	1
23	2	B	1/4/2018	1	2018	4	2
24	2	B	1/5/2018	1	2018	5	9.5
25	2	B	1/6/2018	1	2018	6	10.5
26	2	B	1/1/2018	1	2018	1	0.4
27	2	B	1/1/2018	1	2018	1	3
28	2	B	1/9/2018	2	2018	2	4
29	2	B	1/10/2018	2	2018	3	5
30	2	B	1/11/2018	2	2018	4	3

As a result I want to see the following Calculated Column:

User ID	User Name	Entry Date	Entry Week Num	Entry Year	Entry Week Day	Duration	CumSum
1	A	1/1/2018	1	2018	1	0.5	0.5
1	A	1/1/2018	1	2018	1	0.7	1.2
1	A	1/2/2018	1	2018	2	1.5	2.7
1	A	1/2/2018	1	2018	2	2.5	5.2
1	A	1/3/2018	1	2018	3	3.5	8.7
1	A	1/4/2018	1	2018	4	4.5	13.2
1	A	1/5/2018	1	2018	5	1	14.2
1	A	1/6/2018	1	2018	6	2	16.2
1	A	1/9/2018	2	2018	2	9.5	9.5
1	A	1/9/2018	2	2018	2	10.5	20
1	A	1/10/2018	2	2018	3	0.4	20.4
1	A	1/10/2018	2	2018	3	3	23.4
1	A	1/10/2018	2	2018	3	4	27.4
1	A	1/10/2018	2	2018	3	5	32.4
1	A	1/11/2018	2	2018	4	3	35.4
1	A	1/11/2018	2	2018	4	0.5	35.9
2	B	1/2/2018	1	2018	2	0.7	0.7
2	B	1/2/2018	1	2018	2	1.5	2.2
2	B	1/2/2018	1	2018	2	2.5	4.7
2	B	1/2/2018	1	2018	2	3.5	8.2
2	B	1/2/2018	1	2018	2	4.5	12.7
2	B	1/3/2018	1	2018	3	1	13.7
2	B	1/4/2018	1	2018	4	2	15.7
2	B	1/5/2018	1	2018	5	9.5	25.2
2	B	1/6/2018	1	2018	6	10.5	35.7
2	B	1/1/2018	1	2018	1	0.4	36.1
2	B	1/1/2018	1	2018	1	3	39.1
2	B	1/9/2018	2	2018	2	4	4
2	B	1/10/2018	2	2018	3	5	9
2	B	1/11/2018	2	2018	4	3	12

Will appreciate any valuable information.

Thanks a lot!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this formula, please.

CumSum =
CALCULATE (
    SUM ( Table1[Duration] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[User Name], Table1[Entry Week Num] ),
        'Table1'[Entry Week Day] <= EARLIER ( Table1[Entry Week Day] )
            && Table1[Time Entry ID] <= EARLIER ( Table1[Time Entry ID] )
    )
)

Best Regards,

Dale

[Anonymous]

Big thanks, @v-jiascu-msft!!!

I expect to see something similar in the end but could you, please, explain, why Week Num and User Name are in ALLExcept section and Week Day and Time Entry Id participate in EARLIER calculations. Just don't get this point.

All in all, thanks alot for you reply.

[v-jiascu-msft]

It's my pleasure. @Anonymous.

You still can use this one which has a clearer logic. 

CumSum 2 =
VAR currentWeekDay = [Entry Week Day]  // the mean is as its name.
VAR currentTimeID = [Time Entry ID]
RETURN
    CALCULATE (
        SUM ( Table1[Duration] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[User Name], Table1[Entry Week Num] ),
            'Table1'[Entry Week Day] <= currentWeekDay
                && Table1[Time Entry ID] <= currentTimeID
        )
    )

The main difference here is the Row context in the data table while it's Filter context in a visual. ALLEXCEPT converts the row context into the filter context and clear other contexts except Table1[User Name] and Table1[Entry Week Num].

Best Regards,

Dale

[Anonymous]

thansk, @v-jiascu-msft.

Now it's clearer after your explanation. 

Appreciate your help.