Forum Discussion

someone's avatar
someone
Frequent Visitor
8 years ago
Solved

Using a Slicer to Always Display Previous 4 Rows without

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.

 

Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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's avatar
      someone
      Frequent Visitor
      8 years ago

      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:

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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