How to add up multiple selections from a slicer

[Steve_Scotland]

I am probably showing my ignorance in this question expecting it to work when it doesn't :

I have a measure to lift Line Value from a SQL table Invoices

Sales Value = sum(Invoices[Line_Value])

I have Var in each further measure for

Reporting Week_TY / Reporting_Year_TY / Reporting Week_LY / Reporting_Year_LY

(where TY and LY are this year and last year respectively)

which has been suggested to me I change to measures too but that is a separate issue.

This then allows me to do further measures such as Example 1 to 2 where the only difference is whether I am calculating weeks weeks  this year or Last year in the 'Calc' bit. I'd like to to maintain my code integrity as I have other measures such as one for Profit that says - inevitably -

Profit = sum(Invoices[Profit])

so all I have to do is change my respective codes from [Sales Value] to [Profit]

Everything works swimmingly to calculate

Wholesale Sales Week This Year / Wholesale Sales Week Last Year

with a slicer for the year and week number which of course feeds in to

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

the problem is that the users now want to choose multiple weeks on the slicer so that if they choose weeks 15 / 16 / 17, say, the this year and last year are the sum of the chosen weeks for this year and last year

When I do it, this year is zero and last year is a sum which is not the combination

****************************

//  Example 1

Wholesale Sales Week This Year =

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])

VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1

Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1

// The Calc bit :

VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_TY && Dates[Year Int] = Reporting_Year_TY),

[Sales Value])

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

*************************************

// Example 2

Wholesale Sales Week Last Year =

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])

VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1

Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1

// The Calc bit :

VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_LY && Dates[Year Int] = Reporting_Year_LY),

[Sales Value])

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

****************

[Steve_Scotland]

@Greg_Deckler 

Thank you.

Unfortunately that went right over my head at the moment 😞

I was hoping to change my code slightly to maintain the integrity and re-usability with all my date VAR and subsequent calcs where in the 'Calc bit' above   "all" I had to do was change the date range and what I was scanning for   (Sales / Profit / Budget etc)

Took me an absolute age to get to where I am now.  They said they wanted Week / Period / YTD for Sales / Profit versus last year and versus Budget  and now they don't.  Or rather it is "Yes, but........."

PS:  I have your book 🙂

I've been written to by someone famous!

Last time that happened to me was when Miriam Margolyes wrote to me on CompuServe, he says showing his age.

[Greg_Deckler]

@Steve_Scotland You can't use SELECTEDVALUE when multiple values are selected. You will want to use SELECTCOLUMNS to to return your values as a single column table. You could also use DISTINCT (column form) or VALUE (not recommended). You can then use the IN operator against this table var to filter on.