Compare this year vs last year based on two dates

[stampgeorge]

Hello, 

I'm creating a reservation report for a hotel.

I need to calculate the room nights, revenue etc, and compare this year to last year.

The problem is with last year. I need to only sum the data I need based on booking date and the arrival date.

For example, I want to compare 2025 with 2024 at 30/6/25. 2025 is current data so it's ok.

For 2024, I need all the bookings made from 1/1/23 until 29/6/24, that concern arrivals for last year 1/1/24 - 31/12/24.

I've tried many filters and dax expressions but it doesn't work.

The goal is to use them with the new cards as reference.

Thanks!

[v-nmadadi-msft]

Hi @stampgeorge ,

As we haven’t heard back from you, we wanted to kindly follow up to check if its possible to provide sample data, so that we can investigate it. 

Thanks and regards

[v-nmadadi-msft]

Hi @stampgeorge 

As we haven’t heard back from you, we wanted to kindly follow up to check the status of your issue, if it is still unresolved, Kindly share some sample data so that we can take a closer look and try to assist you further?

Thanks and regards

[v-nmadadi-msft]

Hi @stampgeorge 

May I check if this issue has been resolved? If not, could you please share some sample data so we can investigate further and provide more targeted assistance?

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @stampgeorge ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
Thank you

[miTutorials]

If you need to compare using 2 different slicers here is a video - let me know if this helps!

Compare any week/Year using 2 Slicers as input in PowerBI | MiTutorials

[DataVitalizer]

Hi @stampgeorge 

Are you willing to hardcode the date to achieve your goal, so I can adapt my suggestion accordingly?

Did it work ? 👍 A kudos would be appreciated ‌‌📢 Mark it as a solution to help spreading knowledge

[stampgeorge]

I'd prefer not to, so I could use a year slicer that updates the data dynamically.

[MasonMA]

@stampgeorge 

Hi, could you provide us with those DAX expressions you tried that are not working ?

[stampgeorge]

Room Nights3 = CALCULATE(SUM(Statistics[Rm Nights]), USERELATIONSHIP(DateTable[Date], Statistics[Business Date]), DATESBETWEEN(Statistics[Arrival Date], DATE(YEAR(TODAY()),1,1), DATE(TODAY(),12,31)), DATESBETWEEN(Statistics[Booked Date], DATE(YEAR(TODAY())-1,1,1), TODAY() - 1))

[MasonMA]

Maybe try modifying your filter context this way ?

FILTER(
        Statistics,
        Statistics[Arrival Date] >= DATE(YEAR(TODAY()), 1, 1)
            && Statistics[Arrival Date] <= DATE(YEAR(TODAY()), 12, 31)
    ),
FILTER(
        Statistics,
        Statistics[Booked Date] >= DATE(YEAR(TODAY()) - 1, 1, 1)
            && Statistics[Booked Date] <= TODAY() - 1
    )

[stampgeorge]

It doesn't seem to work properly, the number it calculates is closer to the arrivals of the year (but not the correct number) than the room nights.