Forum Discussion

GebauerAnalytic's avatar
GebauerAnalytic
Frequent Visitor
6 years ago

Toggle Month values into 3 Month Average

Greetings!

 

I have an entire dashboard set up with simple tables like this:

 

Company2019 Units2020 UnitsVar
Apple100200100
Bose200300100
Chiquita300400100

 

Controlled by a radio slicer of Sales Rep and a checkbox slicer of months, and feeding from a datatable like this:

 

CompanyProductUnitsOrder DateSales Rep
BoseApricot205/1/2019Adam
AppleBanana405/1/2019Bella
ChiquitaCantaloupe525/1/2019Cornelius
BoseCantaloupe305/1/2019Bella
AppleApricot116/1/2019Cornelius
ChiquitaBanana666/1/2019Adam

 

I have been tasked with creating a radio slicer to add to this that would offer three options: Month, Rolling 3 Month, Rolling 6 month. The existing slicers are already filtering by month and Sales Rep.

 

Currently, if I had January and February selected, the value in the report would represent January units plus February units. The Month radio button would allow this data to pass through unmodified.

 

The Rolling 3 month radio button should change that output into an average of last November units, last December units, and last January units PLUS an average of last December units, January units, and February units.

 

Likewise, if the Rolling 6 month radio button was selected, it would change a January and February selection on the checkbox slicer of months to: An average of last August, last September, last October, last November, last December, and January units PLUS an average of last September, last October, last November, last December, January, and February units.

 

How would I create such a radio slicer that would interact in the desired way with a month checkbox slicer in which one, two, or twelve months can be selected?

 

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Create a disconnected table for your slicer. Then use a measure like this:

     

    Measure to Show = 
  VAR __Selected = SELECTEDVALUE('SlicerTable'[Column])
RETURN
  SWITCH(__Selected,
    "3 Month",[3 Month Measure],
    "12 Months",[12 Month Measure],
    <etc.>
  )
    • GebauerAnalytic's avatar
      GebauerAnalytic
      Frequent Visitor
      6 years ago

      All of my slicers are already set up as tables, but all are attached to something. By keeping this one isolated, it isn't making any alterations at all, let alone playing well with the other two slicers. Perhaps I need to provide more detail.

       

      My data table has a many-to-one cross-directional relationship with my calendar table and my Sales Rep table (just a list of names). There are dozens of other moving parts that aren't relevant to my current question such that I can't just start unlinking tables willy-nilly. The month slicer feeds directly off the calendar table, and the Sales Rep slicer feeds directly off the Sales Rep table. The measures I'm using on the data table are as follows: 

       

      Rolling 3 Month = VAR LastDate_ = LASTDATE(DataTable[ORDER DATE]) RETURN CALCULATE(AVERAGEX(VALUES('CalendarTable'[Monthnumber]), CALCULATE(SUM(DataTable[UNITS]))), FILTER(ALL('CalendarTable'), 'CalendarTable'[Date] <= LastDate_ && 'CalendarTable'[Date] > DATEADD (LastDate_, -3, MONTH)))
       
      Rolling 6 Month = VAR LastDate_ = LASTDATE(DataTable[ORDER DATE]) RETURN CALCULATE(AVERAGEX(VALUES('CalendarTable'[Monthnumber]), CALCULATE(SUM(DataTable[UNITS]))), FILTER(ALL('CalendarTable'), 'CalendarTable'[Date] <= LastDate_ && 'CalendarTable'[Date] > DATEADD (LastDate_, -6, MONTH)))
       
      Is it possible to get a drag-and-drop filter that will play nicely with my other two filters but also add filtration to tables of measures like:
      2020 = CALCULATE(SUM(DataTable[UNITS]), FILTER(DataTable, YEAR(DataTable[ORDER DATE])=2020))    ?
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can create rolling measure using date calendar like this

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,MONTH))  

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) 
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6,MONTH))

     

    Refer to this how to shift/toggle measures

     

    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115