Showing Detail Rows of Rolling Average

[WZorn]

I have a single data table that has two columns: Period and Value.

Period	Value
01	1
02	4
03	2
04	3
05	6
06	3
07	1
08	1

The first visual is a table that displays a measure that calculates a 4 period rolling average of the value column.

Period	Rolling Average
01	0
02	0
03	0
04	2.5
05	3.75
06	3.5
07	3.25
08	2.75

I would like to have a second table visual that displays the 4 rows that make up the rolling average when one of the rows from the first visual is selected.  For example:  If Period 06 is selected in the first visual, the second visual should show the row data for periods 03, 04, 05, and 06.

I can't figure out how to filter the second visual to only those 4 rows.

Any help would be greatly appriciated.  I've googled and can't quite find what I'm looking for.

[Greg_Deckler]

@WZorn You essentially want what I refer to as a Complex Selector. This would be easier if you had an Index or you made a duplicate of your Period (assume it is text given the leading zero). The Complex Selector - Microsoft Fabric Community. In any even, probably something along these lines. However, the real trick in your case is to ensure that selecting a Period in the first visual does not prematurely filter the second visual so that will likely need a disconnected table. Let me take a quick look and see if I can get a working PBIX file going.

[WZorn]

@Greg_Deckler I've read the link you provided.  Yes, I've started following that exact path.  I've even disconnected the second table visual from the first.  The problem is that I haven't figured out how to "pass" the selected period from the first visual to the second.  I feel like the only way to do it is to have a second data table.  I was trying to avoid duplicating data.  I'm curious to see what you come up with.

[Greg_Deckler]

@WZorn Sorry, got sidetracked yesterday. Here is an example PBIX (attached below signature). See what you think.

[WZorn]

@Greg_Deckler  Part of the way there.  It gives the 4 underlying periods that make up the rolling average in the first table, but it dosen't give the values for those 4 periods.  When you add the value field, it duplicates the value in the final period across all 4 periods.

In this, I've selected Period 05:

Here is the underlying data.  Period 05 has a value of 6.

[Greg_Deckler · Accepted solution]

@WZorn Yep, got it. You just need to create an alternate Rolling Average measure for use in your second table. Updated PBIX is attached.

Rolling Average Alt = 
    VAR __Period = MAX('Periods'[Period]) * 1
    VAR __Table = ADDCOLUMNS( ALL('Table'), "__Period", [Period] * 1 )
    VAR __Rows = FILTER( __Table, [__Period] <= __Period && [__Period] >= __Period - 3 )
    VAR __Result = IF( COUNTROWS( __Rows) >= 4, AVERAGEX(__Rows, [Value]), 0)
RETURN
    __Result