Check if two date ranges match

[shona]

Hello!

Fairly new to DAX so hoping someone can help me.  Suppose I have a report with online orders and I have special offers which run for certain periods, I want to check if an offer was active within the filtered date range for the report.  

This is so that I can check to see how many offers were running within a period against how many were used / purchased.

I have report level filters for the order date and have the offers and offer start and end dates stored in a separate table.

Hope this makes sense.

Thanks,
Shona

[shona]

Hi Charlie,

I've a table with the list of Restaurant Offers, with a unique ID, their start date and end date.

I've then got a table with Restaurant Bookings again with unique IDs, booking date, date booked for, booking time, etc.

I've also got a bookings calendar which is linked to my main restaurant bookings table via the date column - I have report level filters which allow the user to filter the report based on current week, current month, etc.

I need to be able to filter the list of restaurant offers to ones which were only active within the selected booking period, i.e the start and end dates are within the dates of my report filter.

I've since removed the relationship between my bookings table and offers table as I realised this was filtering offers which had previously been booked so I've no relationship present between these two tables now.

I am trying to create a column which indicates if offer is active in filtered date range, so something like this:

active = if(pbrestaurantoffers[startdate] <=max (calendar[date]) && pbrestaurantoffers[enddate] >=min (calendar[date]), "True", "False")

I hope this makes sense.

Thanks.

[v-caliao-msft]

Hi Shona,

It's hard for us to give you the details solution based on the limited informaton, please provide us some sample data and the expected result so that we can make further analysis.

Regards,

Charlie Liao

[ankitpatira]

@shona Do you have relations between these two tables ie between table that contains order date and offers table ? If there is a relation there its easy. You can use fields from Offers table to build visuals and they will filter based on selection of order date on report level filter.

[shona]

The offer ID is stored in my offers table and my orders table so I have a relationship between these. I wanted to check if there were offers active in the booking period which weren't purchased though. How do I do that if there's no relationship between the two dates? Apologies if I'm missing something.