Date Filter within Measure

[droupa]

Hi,

I want to add 2 filters onto a measure.

I have a table with reservations records that have date fields for "Arrival date" and "Booking date"

This table is linked to a calendar table via an active relationship between "Date" and "Arrival date"

I want to compare current period revenue with prior period revenue, but exclude reservations in my prior year comparative that have a booking date within 1 year of the last current booking date (in other words, to compare with where we are now, with where we were at the same date this time last year)

I wanted to use the following measure, but it shows as bank on my visualization:

PY YTD Total Revenue = calculate(

[Total Revenue],SAMEPERIODLASTYEAR('Calendar'[Date]),

filter(Reservations,Reservations[Booking Date]<=dateadd(LASTDATE(Reservations[Booking Date]),-1,year))

)

The measure [Total Revenue] within this formula is:

Total Revenue = sumx(filter(Reservations,Reservations[Notes2]="Reserved"),Reservations[Revenue ex VAT])

Below is a link to my .pbix file.

Sales Report

Does anyone know what I am doing incorrectly?

The tab I am working on is "Cumulative Sales Report - Actual vs Prior year

Thanks!

[v-juanli-msft]

Hi @droupa 

I make a simple test, is my understanding correct?

Best Regards
Maggie

[droupa]

Hi Maggie,

What format is your date in? MM/DD/YYYY?

Basically, if the last booking date in my data is 10 April 2018, then bookings with a booking date greater than 10 April 2017 should be excluded from the comparitive figure.

The logic is that at the same point in time one year ago, those bookings had not yet been made, so I want to have a fair comparison.

Thanks!