Forum Discussion
Measure not working when multiple weeks are selected in slicer
I was able to get a solution from another source, as the one proposed got me close to my solution but not quite there. Below is my new measure that gives the sum of total sales if one week is selected, or if more than one week is selected.
TTL PREVIOUS SALES =VAR SelectedWeeks = VALUES('DATES'[WEEK])VAR SelectedYear = SELECTEDVALUE('DATES'[YEAR])VAR LastYear = SelectedYear - 1VAR ShiftedSales =SUMX(SelectedWeeks,VAR CurrentWeek = [WEEK]VAR ShiftedWeek = CurrentWeek + 1RETURNCALCULATE([TTL SALES ],FILTER(ALL('DATES'),'DATES'[WEEK] = ShiftedWeek &&'DATES'[YEAR] = LastYear)))RETURNShiftedSales
Super Useryou need to decide if you want to limit the users to single selection (in which case your current approach is ok) or if you want to keep it flexible in which case you would need to use VALUES() instead of SELECTEDVALUE(), and you would need to clarify what you mean by "previous" when selecting multiple, potentially not even adjacent, weeks.
lbendlin
To clarify, "previous", for this report is to always show last year same week number.
And you are correct, I would like to be flexible with the selection for WEEK NUMBERS.
When I substitute VALUES for SELECTEDVALUES, I receive an error that says
A single value for column 'NEW WEEK' in table 'DATES' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.- lbendlin
o clarify, "previous", for this report is to always show last year same week number. And you are correct, I would like to be flexible with the selection for WEEK NUMBERS.These seem to be contradictory statements to me.
In my visual there needs to show the Week this year vs the Week last year.
Users are to have the flexibility to choose more than one week in the year and the visual should show the sum of the weeks this year vs the sum of the same weeks last year.