Forum Discussion

Huggibear's avatar
Huggibear
New Member
3 years ago

Remove certain weeks values from Calculate Formula

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

Need Help
Tips and Tricks

1 Reply

  • kpost's avatar
    kpost
    Solution Sage
    3 years ago

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