Forum Discussion
Measure not working when multiple weeks are selected in slicer
I have a calendar table and a sales table.
VAR CurrentWeek = SELECTEDVALUE('DATES'[NEW WEEK])
VAR CurrentYear= SELECTEDVALUE('DATES'[YEAR])
VAR MAXWEEKNUMBER = CALCULATE(MAX('DATES'[NEW WEEK]),ALL('DATES'[Date]))
RETURN
SUMX(
FILTER(ALL('DATE'),
IF( CURRENTWEEK = 1,
'DATES'[WEEK_NUM] = MAXWEEKNUMBER && 'DATES'[YEAR] = CurrentYear -1,
'DATES'[WEEK_NUM] = CurrentWeek+1 && 'DATES'[YEAR] = CurrentYear-1) ),
[TTL LY SLS] )
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
9 Replies
- DataNinja777
Super User
Hi WILLIAMSKEY3 ,
The issue with your current DAX measure is that it relies on SELECTEDVALUE, which only returns a value when a single week is selected in the slicer. When more than one week is selected—like Week 13 and 14—SELECTEDVALUE returns blank, which causes your logic to break down. This is why your previous year sales bar disappears or shows incorrect values. To fix this, you need to rewrite the measure so that it calculates the previous year’s sales dynamically for each selected week, rather than assuming only one week is selected. The revised formula iterates through all selected weeks using VALUES, and then for each week, it calculates the matching week from the prior year using TREATAS to apply those filters directly.
Here’s a corrected version of your measure:
LY TTL SLS = SUMX( VALUES('DATES'[NEW WEEK]), VAR ThisWeek = 'DATES'[NEW WEEK] VAR ThisYear = MAX('DATES'[YEAR]) VAR LastYear = ThisYear - 1 RETURN CALCULATE( [TTL LY SLS], TREATAS({ThisWeek}, 'DATES'[NEW WEEK]), TREATAS({LastYear}, 'DATES'[YEAR]) ) )This formula ensures that each selected week is processed individually, fetching the corresponding week number from the prior year, and summing up the [TTL LY SLS] accordingly. This will fix the issue where selecting multiple weeks causes previous year data to vanish or misbehave in your visuals.
Best regards,
Thanks DataNinja777
for explaining. I did not know about the TREATAS function,. However, this still did not work for report. I showed blank. Is there anything else that I could be missing?
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- lbendlin
you 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.
- v-csrikanth
Community Support
Hi WILLIAMSKEY3
As highlighted by lbendlin , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.
Best Regards,
Community Support Team _ C Srikanth.
