previous 12 weeks filter not working when i add item in row of matrix

[Hamza_Ghannem]

hey,
i created this dax measure to filter my matrix to show only 12 previous weeks based on the selected week from my slicer:

i used WEEK/YEAR as column in my matrix and sum(sales) in values . then i filtered my matrix using the following measure by setting it to 1. until now things are good and it's working, but when i add ITEMS to the rows in my matrix it breaks and nothing was correct . i guess the issue is with my measure so could you please help me on this.?

Last 12 Week Filter = 
VAR _sele = SELECTEDVALUE(weeksTable[weekyear])
VAR _selyear = SELECTEDVALUE(weeksTable[year])
VAR _previousYear = _selyear - 1

VAR _weekNum = SELECTEDVALUE(SALES_SUMMARY_VIEW[WEEK])

VAR _maxWeekPreviousYear = 
    MAXX(
        FILTER(
            ALL('SALES_SUMMARY_VIEW'),
            'SALES_SUMMARY_VIEW'[Year] = _previousYear
        ),
        'SALES_SUMMARY_VIEW'[Week]
    )

VAR _isCrossYear = _weekNum <= 12

RETURN
IF(
    _isCrossYear,
    IF(
        (MAX('SALES_SUMMARY_VIEW'[Year]) = _previousYear && 
         MAX('SALES_SUMMARY_VIEW'[Week]) > _maxWeekPreviousYear - (12 - _weekNum)) ||
        (MAX('SALES_SUMMARY_VIEW'[Year]) = _selyear && 
         MAX('SALES_SUMMARY_VIEW'[Week]) <= _weekNum),
        1,
        0
    ),
    IF(
        MAX('SALES_SUMMARY_VIEW'[Year]) = _selyear &&
        MAX('SALES_SUMMARY_VIEW'[Week]) >= _weekNum - 11 &&
        MAX('SALES_SUMMARY_VIEW'[Week]) <= _weekNum,
        1,
        0
    )
)

[hnguy71]

Hi @Hamza_Ghannem 

May I suggest adjusting your week slicer table a bit? It'll make all your DAX formulas that much more cleaner.

Let's make it easier by adding a rank or index column to your slicer table. Here's a sample calculated table that you can use to test:

SlicerTable = 

// make yours more dynamic with real data and from date table.
VAR _Base = CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31))

VAR _AddCols = 
GENERATE(
    _Base,
    ROW(
        "WeekNum", WEEKNUM([Date]),
        "Year", YEAR([Date]),
        "WeekYear", VALUE(YEAR([Date]) & FORMAT(WEEKNUM([Date]), "00")),
        "WeekRank", RANKX(_Base, VALUE(YEAR([Date]) & FORMAT(WEEKNUM([Date]), "00")),,ASC,Dense)
    )
)

RETURN

_AddCols

You should now have a ranked value for each of your weeks in your model. Then, all that's left is to make a much easier measure to return the last 12 weeks in context:

Last 12 Weeks = 

-- Return the week index in context
VAR _ContextWeek = MAX(SlicerTable[WeekRank])

-- How many weeks do you want to retrieve?
VAR _LastWeeks = 12

-- Let's find the earliest date in context
VAR _StartDate = CALCULATE(MIN(SlicerTable[Date]), FILTER(ALL(SlicerTable), SlicerTable[WeekRank] = _ContextWeek - _LastWeeks - 1 ) )

-- Let's find the last date in context
VAR _EndDate = MAX(SlicerTable[Date])

RETURN

-- dynamically sum and aggregate values in for dates in context.
CALCULATE( SUM(SALES_SUMMARY_VIEW[YOUR_VALUES_COLUMN]), KEEPFILTERS(YOUR_DATE_TABLE[Date] >= _StartDate && YOUR_DATE_TABLE[Date] <= _EndDate))

and, it's that easy!

[Hamza_Ghannem]

@hnguy71  thanks for your asnwer , 
can you please explain , which columns to use in my matrix? i'm using
In Matrix
Column : sales_summary_view[weekyear] 
Rows: sales_summary_view[ITMDES]
Values : Last 12 Weeks
In slicer :

i used  slicerTable[weekyear]

Link: 1 to Many active relationship 

Do i have to use my date table in the Last 12 weeks measure? this is confusing me

[hnguy71 · Accepted solution]

Hi @Hamza_Ghannem ,

Your weekyear as matrix column is okay, but I would actually put Week Number and Year instead. This should ideally be coming from your date table. Your ITMDES field in the rows is okay as well. The [Last 12 Weeks] measure as values is okay as well.

Hopefully your slicer table is a disconnected, meaning there's no relationship to it, BUT, IF your slicer table is actually your date table then you need an additonal table that is disconnected to retrieve the user selected value(s).

You don't have to use a date table in your [Last 12 Weeks] measure but I would assume you would be using that for all your time intelligence measures (as that's typically a best practice). However, if you're not, you should be using a date field from your fact table instead.

Anyhoo, without seeing your actual model, I'm just going off of assumptions and guesses. If you're still having issues, you should consider uploading a sample model or share relevant screenshots that gives more context to how everything fits together.