Using measures to return data grouped by specific date ranges

[Alex_Ooi]

Hi folks,

I want to create a measure called Total Bookings by Campaign Period, which I am stuck at the moment. The closest I could get to this is using the measure below:

Overall # Bookings = CALCULATE([# Bookings],
    ALLEXCEPT(Sales,Sales[Site Name],dCalendar[Calendar Date]))

By filtering my date ranges on the slicer to be the same with the campaign period, I would expect the first row to return a result of 4,354.

 

My challenge is to group the booking counts (that contains booking dates in Sales table) into Campaign Start Date and Campaign End Date (which are from the Promo table). I tried writing this but it returns an error for me.

Total Bookings by Campaign Period = CALCULATE([# Bookings],
    ALLEXCEPT(Sales,Sales[Site Name],
        DATESBETWEEN(Sales[Booking Date], PromoCodes[Booking From], PromoCodes[Booking To]))

 Below is the error message:

"A single value for column 'Booking From' in table 'PromoCodes' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I have no idea why RELATED does not work in my formula above either. After many rounds of research, I am still not able to find light at the end of the DAX tunnel.

Please advise. Thanks.