Cumulative Sum by weekday restarting each week (Week To Date)

[JonHacking]

Hi,

My dataset is set up as below:

I wish to add a column/measure which would show a Week To Date value each day as below:

WeekDate    WeekDay    field     WeekToDate
Week1          1                 1          1

Week1          2                 3          4
Week1          3                 0          4 

Week1          4                 1          5

Week1          5                 2          7
Week1          6                 0          7

Week1          7                 1          8

Week2          1                 3          3
Week2          2                 0          3

Week2          3                 1          4

Week2          4                 3          7
Week2          5                 0          7

Week2          6                 1          8

Week2          7                 1          9

I've tried the following: 

CALCULATE(

SUM('Table'[fieldname]),

FILTER(

'Table',

'Table'[WeekdayNum]<=MAX('Table'[WeekdayNum])

)

This just gives me the same as field name

and I've also tried :

CALCULATE(

SUM('Table'[fieldname]),

FILTER(

ALLEXCEPT('Table', 'Table'[Week Date]),

MIN('Table'[WeekdayNum]) IN {1,2,3,4,5,6,7}

)

)

Which shows be the total week value for each day.

Any help would be greatly appreciated.

Cheers,

Jon

[JonHacking]

My dataset is set up as below:

I wish to add a column/measure which would show a Week To Date value each day as below:

WeekDate    WeekDay    field     WeekToDate
Week1          1                 1          1

Week1          2                 3          4
Week1          3                 0          4 

Week1          4                 1          5

Week1          5                 2          7
Week1          6                 0          7

Week1          7                 1          8

Week2          1                 3          3
Week2          2                 0          3

Week2          3                 1          4

Week2          4                 3          7
Week2          5                 0          7

Week2          6                 1          8

Week2          7                 1          9

I've tried the following: 

CALCULATE(

SUM('Table'[fieldname]),

FILTER(

'Table',

'Table'[WeekdayNum]<=MAX('Table'[WeekdayNum])

)

This just gives me the same as field name

and I've also tried :

CALCULATE(

SUM('Table'[fieldname]),

FILTER(

ALLEXCEPT('Table', 'Table'[Week Date]),

MIN('Table'[WeekdayNum]) IN {1,2,3,4,5,6,7}

)

)

Which shows be the total week value for each day.

Any help would be greatly appreciated.

Cheers,

Jon

[parry2k · Accepted solution]

@JonHacking try this

CALCULATE(
SUM('Table'[fieldname]),
FILTER(
ALLEXCEPT('Table', 'Table'[Week Date]),
'Table'[WeekdayNum] <= MAX( Table[WeekDayNum[)
)
)

[JonHacking]

That has worked

Cheers, 

Jon