Need help to create dynamic matrix table filter with slicer date and parameter

[rchdchiu]

Good day, 

I would like to create a dynmic matrix table based on slicer date and parameter of week#

Here is the matrix I created currently with hard code:

-----

>> Matrix content:

Matrix column: Slicer_date, Slicer_date+1week, Slicer_date+2week, ..., Slicer_date+10week
Matrix row: team

Matrix value: %test_done

-----

>> Items created
1. slicer: slicer_date[Date] 

2. 10 measures:

%test_done_Slicer_date, 

%test_done_Slicer_date+1week, 

%test_done_Slicer_date+2week, 

...

%test_done_Slicer_date+10week

>> the measure major body is : 

%test_done_Slicer_date =
var _maxDate = MAX(Slicer_date[Date])
var PassedCount =
CALCULATE(
COUNTROWS(tbl_test_case),
FILTER( 
ALLSELECTED(tbl_test_case[testcaseResult_execEndDate]),
(tbl_test_case[testcaseResult_execEndDate] <= _maxDate)
)
)

var PlannedCount =
CALCULATE(
COUNTROWS(tbl_test_case),
FILTER(
ALLSELECTED(tbl_test_case[testcaseRun_plannedEndDate]),
(tbl_test_case[testcaseRun_plannedEndDate] <= _maxDate)
)
)
return
if(PlannedCount=0 || PassedCount=0 || PlannedCount<>0 && PassedCount<>0 && PlannedCount - PassedCount < 0,
0,
IF(PlannedCount<>0 && PassedCount<>0 && PlannedCount = PassedCount,
100,
PassedCount/PlannedCount * 100)
)

>>for +1week, 2week, ..., 10week, I manually change to _maxDate+7, _maxDate+14,..., _maxDate+70

-----

After adding 10 measures into Matrix, the data change based on the selection of Slicer_date. 

Right now, I got new requirement that client would like to enter the number weeks from 1-20 and Matrix will show column: Slicer_date, Slicer_date+1week, Slicer_date+2week, ..., Slicer_date+20week dynamically.

What I can think of is to add parameter: week  #, but not sure how to use it to control the matrix since slicer_date only works on measure and they are manually created. Can I add this parameter into my current version, or I need to re-write my current matrix from scratch?

Any suggestion is welcomed.

Thanks in advance.

[amitchandak]

@rchdchiu , If want to select a date or a week, and want to display more, you need to have a slicer on disconnected date/week table. You will have joined date, Disconnected date and disconnected parameter table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _min= minx(allselected(Date1),Date1[Week Rank])
var _max = _min + [Parameter Meausre]
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

Where week Rank can be created on week start date or YYYYMM as a new column

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg