Measure not working when multiple weeks are selected in slicer

[WILLIAMSKEY3]

Hi, I created a below measure to calculate the previous week sales. It is working fine when I am selecting a single week. But when more than one week is selected, it is not adding up.

Example. I select Week 14 and I get the correct totals and the visuals show both week 14 of 2024 and 2025. However, when I am selecting Weeks 13 and 14, only TY week shows and 2024 column in the bar chart is disappeared and it is showing the latest month data but I need the sum. How can I fix this?
I have a calendar table and a sales table.

LY TTL SLS =

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

[WILLIAMSKEY3 · Accepted solution]

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 - 1

VAR ShiftedSales =

    SUMX(

        SelectedWeeks,

        VAR CurrentWeek = [WEEK]

        VAR ShiftedWeek = CurrentWeek + 1

        RETURN

            CALCULATE(

                [TTL SALES ],

                FILTER(

                    ALL('DATES'),

                    'DATES'[WEEK] = ShiftedWeek &&

                    'DATES'[YEAR] = LastYear

                )

            )

    )

RETURN

    ShiftedSales

[v-csrikanth]

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.

[DataNinja777]

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,

[WILLIAMSKEY3]

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?

[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.

[WILLIAMSKEY3]

@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.

[WILLIAMSKEY3]

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.

[lbendlin]

That makes more sense.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...