Forum Discussion

stampgeorge's avatar
stampgeorge
Frequent Visitor
1 year ago
Solved

Compare this year vs last year based on two dates

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!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

11 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    stampgeorge 

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

    • stampgeorge's avatar
      stampgeorge
      Frequent Visitor
      1 year ago
      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's avatar
        MasonMA
        Icon for Super User rankSuper User
        1 year ago

        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
    )

         

  • DataVitalizer's avatar
    DataVitalizer
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      stampgeorge
      Frequent Visitor
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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