Measure to calculate bookings created based on selected check-in date

[Arnauar97]

Hi everyone,

I’m trying to calculate a measure in Power BI to show the bookings created for a hotel. My goal is that, when selecting a specific month in a page filter (linked to my Dim_Date table), a bar chart distributes the bookings created by their creation date, but only for bookings whose check-in date falls within the selected month.

Currently, I’m using this measure:

BookingsCreated_MonthCreated =
CALCULATE(
DISTINCTCOUNT('Fact_MovRvas'[RESERVA_PK]),
ALLEXCEPT('A0Dim_Fecha', A0Dim_Fecha[Month Name]),
'Fact_MovRvas'[FECHAENTRADA] IN VALUES(A0Dim_Fecha[Date])
)

The page filter is set up to select year and month from the Dim_Date table. However, the measure doesn’t seem to work as expected. While the chart updates when the filter changes, the bars aren’t distributed properly by the creation date (FECHACREACION), and it seems that the bookings aren’t being correctly filtered by the check-in date.

I’d also like to create a similar calculation for canceled bookings, following the same logic but using the cancellation date instead of the creation date.

Could anyone guide me on how to adjust this measure to achieve the desired result? Any help would be greatly appreciated!

Thanks!!

[v-junyant-msft · Accepted solution]

Hi @Arnauar97 ,

Thanks for BeaBF's reply!
And @Arnauar97 , I don't know what your data model looks like, but I think your DAX should be able to meet your needs, but you need to disconnect your table Fact_MovRvas from the table Dim_Date. The following is the sample data I created myself:

There is no relationship between them:

Then use these DAXs to create measures:

BookingsCreated_MonthCreated = 
CALCULATE(
    DISTINCTCOUNT(Fact_MovRvas[RESERVA_PK]),
    ALL(Fact_MovRvas),
    'Fact_MovRvas'[Check-in Date] IN VALUES(Dim_Date[Date])
)

BookingsCanceled_MonthCanceled = 
CALCULATE(
    DISTINCTCOUNT(Fact_MovRvas[RESERVA_PK]),
    ALL(Fact_MovRvas),
    'Fact_MovRvas'[Cancellation Date] IN VALUES(Dim_Date[Date])
)

And the final output is as below:

If this does not solve your problem, please provide sample data of your tables and the relationship between the tables, thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeaBF]

@Arnauar97 Hi! Try with:

BookingsCreated_MonthCreated =
CALCULATE(
DISTINCTCOUNT('Fact_MovRvas'[RESERVA_PK]),
FILTER(
'Fact_MovRvas',
'Fact_MovRvas'[FECHAENTRADA] IN VALUES(A0Dim_Fecha[Date])
),
REMOVEFILTERS(A0Dim_Fecha),
VALUES(A0Dim_Fecha[Month Name])
)

BBF