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:   Company 2019 Units 2020 Units Var Apple 100 200 100 Bose 200 300 100 Chiquita 300 400 100 ...
Need Help
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))    ?