DAX: access filter context of previous / next sele...

iplaygod · ‎03-03-2019

Hi All!
(see image below)
I have created a slicer that allows the user to select sales events to show them beside eachother in a graph.
Each event is one row in an events table.
The selection in the slicer filters the events table.

I have then created a measure that takes ONE event name (with its start dates etc) and makes the calculation based on that one active event. To achieve giving the measure only 1 event to calculate I apply the event name to the x axis of the column chart.
Thereby, for each column, only ONE event will be active in the filter context of that particular column.
So far so good. I can select events and see them appear in the graph. The measures work.

Now for the challange...
I would like to be able to compare the events to eachother, not only by looking at them, but by doing calculations comparing them. (diff %, diff in total sales amount etc)
So for example I would like to be able to compare the 1st event that is selected in the slicer to the 2nd event in the slicer.
The problem is that inside the filter context of the graph, the measure only sees ONE row of the event table, because that is the filter being applied by the graph (x-axis). The measure becomes completely blind as to what other events have been selected in the slicer.

How can I achieve the capability of comparing one event to the other?
I dont want to hardcode values because it needs to be selectable in the slicer.

I would like to be able to say (in pseudo-code)
MyComparison = Total Sales inside current column filter context - Total sales inside filter context of the PREVIOUS column in the graph

I want to avoid writing specific measures for specific events, because that is just stupid 🙂
I would like to handle this in a generic and flexible way to avoid bloating / re-writing a bunch of stuff.

Can you please give me an idea of a "pattern" / way of thinking / or model on how to achieve this?
Thanks alot