Remove certain weeks values from Calculate Formula

[Huggibear]

Hi

I’m looking at doing a formula that excludes a certain week from the overall calculation.

I Need to change/amend the formula below in PBI to exclude week 27

Clients =
VAR THISYEAR = [This Year]
var max_weekLW = CALCULATE(MAX('Customer'[WeekNo]))-1
RETURN
if([Comparison]="TW",
CALCULATE(sum(Customer[Clients]),'Customer'[Year] = THISYEAR ),
if([Comparison]="LW",
CALCULATE(sum(Customer[Clients]),'Customer'[Year] = THISYEAR,'Customer'[WeekNo] = max_weekLW ),
if([Comparison]="MTD",
CALCULATE(sum(Customer[MTD_ Clients]),'Customer'[Year] = THISYEAR ),
if([Comparison]="STD",
CALCULATE(sum(Customer[STD_ Clients]),'Customer'[Year] = THISYEAR ),
if([Comparison]="YTD",
CALCULATE(sum(Customer[YTD_ Clients]),'Customer'[Year] = THISYEAR ),blank())))))

To clarify , I need the value above but to exclude week 27 only from the result

I have attempted the following formula but get an error

Clients Excluding Week 27 =
VAR THISYEAR = [This Year]
var max_weekLW = CALCULATE(MAX('Customer'[WeekNo]))-1
RETURN
if([Comparison]="TW",
CALCULATE(sum(Customer[Clients]),'Customer'[Year] = THIS YEAR, 'Customer'[WeekNo]<>27 ),
if([Comparison]="LW",
CALCULATE(sum(Customer[Clients]),'Customer'[Year] = THISYEAR,'Customer'[WeekNo] = max_weekLW , 'Customer'[WeekNo]<>27 ),
if([Comparison]="MTD",
CALCULATE(sum(Customer[MTD_ Clients]),'Customer'[Year] = THIS YEAR , 'Customer'[WeekNo]<>27 ),
if([Comparison]="STD",
CALCULATE(sum(Customer[STD_ Clients]),'Customer'[Year] = THIS YEAR , 'Customer'[WeekNo]<>27 ),
if([Comparison]="YTD",
CALCULATE(sum(Customer[YTD_ Clients]),'Customer'[Year] = THIS YEAR , 'Customer'[WeekNo]<>27 ),blank())))))

Any support will be greatly appreciated 
Thank You

[kpost]

since you did say "ANY support will be greatly appreciated ".... I can't help with your main question but I do have a useful syntax tip.

Instead of nested IF statements, try a SWITCH statement....  cleaner, easier to read, less commas and parenthesis....

Clients =
VAR THISYEAR = [This Year]
var max_weekLW = CALCULATE(MAX('Customer'[WeekNo]))-1
RETURN
SWITCH(
         TRUE(),
          [Comparison]="TW", CALCULATE(sum(Customer[Clients]),'Customer'[Year] = THISYEAR ),
          [Comparison]="MTD", CALCULATE(sum(Customer[MTD_ Clients]),'Customer'[Year] = THISYEAR ),
          etc....
          BLANK()
)