Toggle Month values into 3 Month Average

[GebauerAnalytic]

Greetings!

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

Company	2019 Units	2020 Units	Var
Apple	100	200	100
Bose	200	300	100
Chiquita	300	400	100

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

Company	Product	Units	Order Date	Sales Rep
Bose	Apricot	20	5/1/2019	Adam
Apple	Banana	40	5/1/2019	Bella
Chiquita	Cantaloupe	52	5/1/2019	Cornelius
Bose	Cantaloupe	30	5/1/2019	Bella
Apple	Apricot	11	6/1/2019	Cornelius
Chiquita	Banana	66	6/1/2019	Adam

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?

[amitchandak]

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

[Greg_Deckler]

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]

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))    ?

[v-lili6-msft]

hi  @GebauerAnalytic 

For your case,  you could refer to this blog:

Dynamically change the information within a visual via a slicer

Regards,

Lin

[GebauerAnalytic]

While this touches on the category of what I need, it doesn't really get into how to dynamically change measures on a page or how to effectively overlap these filters without issue.