several dates

[Tiago_Varela]

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 like this:

The sum of room solds for stays from 01/01/2017 to 31/12/2017, were the criation date is until 01/05/2017 and the cancelation date is until 01/05/2017.

the same for 2018: room solds for stays from 01/01/2018 until 31/12/2018, were the criation date is until 01/05/2018 and the cancelation date until 01/05/2018.

I need a dax formula because i want to see them in a board and then see the diference, so i cannot use filters on the page.

thanks

[jtownsend21]

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]

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]

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?