Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |