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

Need Help
Tips and Tricks

3 Replies

  • 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?