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...
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

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:

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    can you share data in excel ?

    • someone's avatar
      someone
      Frequent Visitor
      8 years ago

      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's avatar
        parry2k
        Super User
        8 years ago

        you can share thru google drive or one drive