Help with PY Comparative

[droupa]

Hi,

I am trying to compare PY revenue on reservations/booking data, but I want to exclude all the reservations that were booked within in year of the current date of the report being run. This is the formula im using in a visualization, but it shows zero values. 😞

PY YTD Total Revenue = sumx(
filter(Reservations,Reservations[Booking Date]<[Booking date PY]),[PY Total Revenue]
)

The measures within this formula are shown below:

Booking date PY = DATE(YEAR(NOW())-1,MONTH(NOW()), DAY(NOW()))

PY Total Revenue = calculate([Total Revenue],SAMEPERIODLASTYEAR('Calendar'[Date]))

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

Hereis the link to my Power BI file:

Sales report v2

Does anyone know what im doing wrong?

Many thanks!

[TeigeGao]

HI @droupa ,

PLease try to use the following DAX query:

PY YTD Total Revenue = sumx(
filter('Calendar','Calendar'[Date]<[Booking date PY]),[PY Total Revenue]
)

Based on my test, we'd better not using multiple date column in this scenario.

Best Regards,

Teige