Filtering x-axis on offset start and end date

[SanderTK]

I am running into a rather unique problem that is throwing me for a bit of a loop:

I have two tables, one table with events that have a colomn for the start date and end date of that event and another (fact) table with ticket sales.

What I would like to do is have a standard bar/line chart with ticket sales on the y-axis and the dates on the x-axis, filtered by a slicer of events. when I select an event in the slicer, I would like to show the ticket sales from let's say 10 days before the start date of the event to 10 days after the end date of that event. 

There are two issues in this:

The first one is that I'm working with two date columns that both need to be used to filter the x-axis of my line/bar chart.

I do not know how to filter the x-axis by a MIN date and MAX date simultaneously.

The second one is the offset in days that I need to apply on the visual because the x-axis should start a certain amount of days before the start date of the selected event and should end that same amount of days after the end date of the event.

I have tried creating a custom column to create the offset but then I still have two columns; one for the offset start date and one for the offset end date. I don't know how to apply both of those to one date range on the x-axis of a visual.

I have also looked at the "range" option on the x-axis to work with the earliest start date (or earliest offset start date) and latest end date (or offset end date) but then I still don't quite understand what date field I should use for the x-axis of my visual and how I connect the two tables in my relational model.

Any and all help/input would be much appreciated!

[v-kongfanf-msft]

Hi @SanderTK ,

Is there any further feedback?

Best Regards,
Adamk Kong

[SanderTK]

Apologies for the late response, finished the work yesterday. 
I have solved this by indeed creating two offset coloms (start and end date of the events) and I have used those for the "range" function of the x-axis, using the date from the date table as the date on the x-axis itself.

The slicer is on productions (one step above events, meaning multiple events can be in one production) which makes it so that the x-axis changes according to the selected value.
The start and end date of the event are shown as vertical lines on the x-axis to indicate the time range of the event on the wider x-axis.
Basically like this:

[Ashish_Mathur]

Hi,

I'd lile to try.  Share data in a format that can be pasted in an MS Excel file.  For that data shared, show the expected result in a simple Table format.

[v-kongfanf-msft · Accepted solution]

Hi @SanderTK ,

You can create two date offsets on the event table. Then use this date range as a filter based on it.

Start Date Offset = Events[Start Date] - 10

End Date Offset = Events[End Date] + 10

Create a MEASURE for calculating ticket sales for the date range.

Total Ticket Sales = 
CALCULATE(
    SUM('Ticket Sales'[Sold]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= MIN(Events[Start Date Offset]) && 'Calendar'[Date] <= MAX(Events[End Date Offset])
    )
)

Also based on your description, the double date range is not friendly for line charts, I think table is a better option. If the problem persists, please provide detailed test data and screenshots of the desired results.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SanderTK]

Thank you so much for the detailed answer. I will have a look at implementing it on Monday! I think I can use a line chart with vertical lines on the x-axis to indicate when an event started and stopped. Will keep you updated.