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 ,

 

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's avatar
    SanderTK
    Advocate II
    2 years ago

    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.