cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tiago_Varela
Helper I
Helper I

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

3 REPLIES 3
jtownsend21
Responsive Resident
Responsive Resident

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

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

 

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? 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors