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())
    )
)
Tiago_Varela's avatar
Tiago_Varela
Helper I
7 years ago

Hi,

 

Thanks for the reply, however it doesn't work.

It gives me an error saying that "it contains several columns and it can only work with one column in a true/false expression"

I already tried the following:

stay_in_2018 = CALCULATE([RN_TOTAL];AND('STAY_DATE'[DATA_ESTADIA]>= DATE (2018;01;01) ; 'STAY_DATE'[DATA_ESTADIA] <=DATE(2018;12;31) ); AND('CRIATION DATE'[DATA_CRIACAO]>= DATE(2012;01;01);'CRIATION DATE'[DATA_CRIACAO]<=DATE(2018;04;29)))
HOWEVER IT GIVES ME A BAD RESULT.
 
Thanks

 

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    My apologies. I forgot about that limitation. The way you wrote yours, you have changed some of the conditions from your original message. My formula below is still using the conditions you outlined originally. 

     

    SUM ROOMS SOLD = 
VAR _RESERVATIONDATE = 
    MONTH(ReservationDate) - MONTH(TODAY())
VAR _CANCELATIONDATE = 
    MONTH(CancelationDate) - MONTH(TODAY())

RETURN
CALCULATE(
    SUM(RoomsSold),
    YEAR(Stay Date) IN {  "2017", "2018", "2019" },
    AND(
        _RESERVATIONDATE < 0,
        _CANCELATIONDATE < 0
    )
)

    I have also added 2019 in the dates. If this doesn't work, could you share a sample workbook or some sample data?