Using a Slicer to Always Display Previous 4 Rows without

[someone]

I have gotten a request to make the report I'm working on always display the previous four quarters (without using multi-select) depending on which selection is made. The data is organized as follows:

  

The report looks like this:

The request is so that when any quarter is selected it displays the previous four rows of quarters:

 

Any guidance and help on this would be greatly appreciate.

[v-juanli-msft · Accepted solution]

Hi @someone

1. In query editor, create an index column from 1.

   Then create a blank query, and write code in Advanced editor.

let
    Source = Table.SelectColumns(Sheet1,{"Quarter","Index"})
in
    Source

2. In Relationship View, create the relationship between two tables.

3. In Report view, create measures, then add [Measure 3] to the Visual Filter and show 1

Measure = SELECTEDVALUE(Query1[Index])

Measure 2 = [Measure]-4

Measure 3 = IF(MAX(Sheet1[Index])>=[Measure 2]&&MAX(Sheet1[Index])<[Measure],1,0)

Best Regards

Maggie

[parry2k]

Does your dataset have a date? If not then you can add a index column and then can be worked thru that. It will be good to know what your dataset looks like before giving the solution.

[someone]

My dataset does not have a date. The quarter format is a column with shorthand for fiscal year (the year) and quarter (quarter number). You can see it in this picture:

[parry2k]

can you share data in excel ?

[someone]

I am can't figure out how to attach a file to a post in this forum. Is there another way that would work?

[parry2k]

you can share thru google drive or one drive

[someone]

http://drive.google.com/open?id=1Qh6iayE1uVULkxKeUvrELSF8ibkJhsb1

Let me know if this doesn't work.