Forum Discussion

droupa's avatar
droupa
Frequent Visitor
7 years ago

Help with PY Comparative

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!

 

Need Help

1 Reply

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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