Filter a table to be able to calculate the 4 rows below the top row

[RaphaelSlalom]

Hello!

I am having some issues here, I need the ability to dynamically filter a table to calculate the average. 

To be more specific, I need to calculate the average of one column of a table (lets call it sales), but I need to be able to select a specific row in the table and be able to change it so it just calculates the row, and the previous 4 entries of that table, by descending date. 

I have tried creating a TOPN table (5), but when i select the row, it filters down the row to the single entry selected, but I would like it to change the table itself dynamically to whichever date is selected in the filter and the 4 previous entries. The filtering is really crucial but also preventing it from working. 

Also tried using the RankX to accomplish the same thing, but it also filters down to one row, and also the RankX does not dynamically adjust to the rows that are visible, it still considers the entire table.

Open to any ideas here, I have been beating my head against the wall for a bit about it.

[RaphaelSlalom]

I'll take a look at this today thanks!

[sturlaws · Accepted solution]

Hi, @RaphaelSlalom,

if you include sample data and a mockup of the desired outcome, you will get faster and more accurate answers.

I have created a mockup report based on your description: report

where you find this measure: 

Average of last 5 days = 
VAR _date =
    IF (
        HASONEVALUE ( 'Table'[Date] ),
        CALCULATE ( SELECTEDVALUE ( 'Table'[Date] ) ),
        BLANK ()
    )
VAR _date4prevDays =
    IF ( ISBLANK ( _date ), BLANK (), _date - 4 )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] >= _date4prevDays
                && 'Table'[Date] <= _date
        ),
        VALUES ( 'Table'[Department] )
    )

which takes the average of the last 5 days, including the selected date. It will also reflect which department you choose.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.