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 d...
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

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
    )

       

      • stampgeorge's avatar
        stampgeorge
        Frequent Visitor
        1 year ago

        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.