Forum Discussion

SanderTK's avatar
SanderTK
Advocate II
2 years ago
Solved

Filtering x-axis on offset start and end date

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 an...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi SanderTK ,

 

Is there any further feedback?

 

Best Regards,
Adamk Kong

SanderTK's avatar
SanderTK
Advocate II
2 years ago

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: