Forum Discussion

Tiago_Varela's avatar
Tiago_Varela
Helper I
7 years ago

several dates

Hi all, hope you can help me. I have a room sales for several hotels and several years with 3 dates: stay date reservation date cancelation date I want to see the sales for 2017, 2018 and 2019 ...
Need Help
Tips and Tricks
jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago

I would try somet hing like the following: 

SUM ROOMS SOLD = 
CALCULATE(
    SUM(RoomsSold),
    YEAR(Stay Date) IN {  "2017", "2018" },
    AND(
        NOT(MONTH(ReservationDate) IN { "5", "6", "7", "8", "9", "10", "11", "12" }),
        NOT(MONTH(CancelationDate) IN { "5", "6", "7", "8", "9", "10", "11", "12" })
    )
)

 If you want it to be dynamic based on the current month, then the following is a better solution. 

SUM ROOMS SOLD = 
CALCULATE(
    SUM(RoomsSold),
    YEAR(Stay Date) IN {  "2017", "2018" },
    AND(
        MONTH(ReservationDate) < MONTH(TODAY()),
        MONTH(CancelationDate) < MONTH(TODAY())
    )
)