Recalculate Index From Slicer Selection and Use Result to Filter Visuals

[jmdublu]

Have searched extensively to try to solve this and finally decided I need to ask for help.  Will try to make this as simple as possible. 

I have a data source at weekly grain for current month to current month + 2.
Base table includes Week Start Date and Number of Weeks From Current Week (calculated in SQL) for each entity. 

Currently filtering 4 visuals where first visual filter is [Weeks From Current]  = 1, second visual is [Weeks From Current] = 2, etc. (dates change each new month; default view requested from business is next week and 3 following weeks).

Now I want the user to be able to select the Week Start Date from Slicer, and visuals will shift to the week selected, plus the next 3 weeks. 

I want to replace current filter with new filter on calculated week: first visual to [New Week Number] = 1, second visual to [New Week Number] = 2, etc. based on selection.

I want to avoid Time Intelligence if at all possible.  What I have done so far:

Created a calculated table (WEEK_BASE) for distinct Week Name and Weeks From Current and made this Slicer source.

Created second calculated table (WEEK_REBASE) from first with working Measures for:

a) Week Selected (either DAX version below works):

- IF(HASONEVALUE(WEEK_BASE[Weeks From Current]),VALUES(WEEK_BASE[Weeks From Current]), BLANK())
- SELECTEDVALUE(WEEK_BASE[Weeks From Current])

b) New Week Number:

- SUMX(WEEK_REBASE, WEEK_REBASE[Weeks From Current] - WEEK_REBASE[w_Selected_Week] + 1)

Created a third calculated table (WEEK_FILTER) from 2nd table and pulled measures as columns:

Herein lies the problem:

- [Week Selected] comes into the new table blank

- [New Week Number] comes into the new table as [Weeks From Current] + 1

* (Since Week Selected is blank it is treated as 0 and only the + 1 is calculated)

* Even table refresh does not update these values

Sample data (where Slicer selected value = 11-03-24):

Period Name	Weeks from Current	Week Selected	New Week Number
Week of: 09-01-24	-2	7	-8
Week of: 09-08-24	-1	7	-7
Week of: 09-15-24	0	7	-6
Week of: 09-22-24	1	7	-5
Week of: 09-29-24	2	7	-4
Week of: 10-06-24	3	7	-3
Week of: 10-13-24	4	7	-2
Week of: 10-20-24	5	7	-1
Week of: 10-27-24	6	7	0
Week of: 11-03-24	7	7	1
Week of: 11-10-24	8	7	2
Week of: 11-17-24	9	7	3
Week of: 11-24-24	10	7	4

Screenshot:

Desired result based on Slicer selection above would be Week of: 11-03-24 and Week of 11-10-24, etc.

Thank you in advance for any possible solution.

[jmdublu]

Thank you very much for your response @v-rzhou-msft.  I will dig into this and see if I can apply same to my situation.

[v-rzhou-msft]

Hi @jmdublu ,

I hope my workaround could help you solve your issue. If it is, please kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your issue or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[jmdublu]

@v-rzhou-msft,
Please kindly review the updated .pbix file here and provide any additional feedback regarding the problem as I've outlined further in my most recent response.  Thank you again, very kindly for your assistance.

[v-rzhou-msft]

Hi @jmdublu,

Here I create a Calendar Table as below.

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 09, 01 ), DATE ( 2024, 12, 31 ) ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 1 ) + 1,
    "Period Name",
        "Week of:" & " "
            & FORMAT ( [Date] - WEEKDAY ( [Date], 1 ) + 1, "MM-DD-YY" )
)

Rank = RANKX(DimDate,[WeekStart],,ASC,Dense)

Week from Current = 
VAR _WeekStart = TODAY() - WEEKDAY(TODAY(),1)+1
RETURN
[Rank] - CALCULATE(MAX(DimDate[Rank]),FILTER(DimDate,DimDate[WeekStart] = _WeekStart))

A Calculated Table like yours:

Table = SUMMARIZE(DimDate,DimDate[Period Name],DimDate[Week from Current])

An unrelated DimPeriod Table:

DimPeriod = 
'Table'

If you want dynamic result, please try measures.

WeekSelected = SELECTEDVALUE(DimPeriod[Week from Current])

New Week Number = CALCULATE(SUM('Table'[Week from Current])) - [WeekSelected] + 1

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmdublu]

Hello @v-rzhou-msft,

I've had a chance to try to put your solution in action and I've run into a problem.  The selection filters the matrix in your sample .pbix, but when I try to apply it to the visuals in my dashboard, I am experiencing the same problem as before- filtering with new week number does not produce expected results (though I can at least modify the filter when I add it to the visual now...).

In the example in the image below, the selection of 10-06-24 should result in Week "1" visual producing values for Week of 10-06-24 and Week "2" visual producing values for Week of 10-13-24, and so on for weeks 3 and 4.  Instead the selection has no effect.

I attempted to attach updated version of your .pbix, but I do not see the option to attach files anywhere in the interface. 

Here is the code I used to generate the sample data that is populating the "Data" table:

Data = 
VAR sites = DATATABLE(
    "Site", STRING,
    { 
        {"A"},{"B"},{"C"},{"D"},{"E"},{"F"},{"G"},{"H"}
    }
)

VAR tbl = 
ADDCOLUMNS(
    CROSSJOIN(SUMMARIZE(sites, [Site]), 
    SUMMARIZE(DimPeriod, DimPeriod[Period Name], DimPeriod[Week from Current])),
    "Fake Actual Volume", ROUND(RAND() * 140000,0)
)

VAR rslt = 
SUMMARIZE(tbl, 
    [Site], 
    [Period Name],
    [Week from Current],
    [Fake Actual Volume],
    "Fake Budget Volume", [Fake Actual Volume] - RANDBETWEEN([Fake Actual Volume] * -0.30, [Fake Actual Volume] * 0.30)
)

RETURN rslt

Since my data is at weekly grain, and does not include day dates, I modified DimDate so that I could link to Data as many:1:

NewDateDim = 
DISTINCT(
    SELECTCOLUMNS(DimDate,
    "Period Name", DimDate[Period Name],
    "Week Start", DimDate[WeekStart],
    "Rank", DimDate[Rank],
    "Week from Current", DimDate[Week from Current]
    )
)

I joined this table to Data on Period Name.  Hopefully this additional information helps clarify my need.

Thank you very much again for your help.