Measure to calculate bookings created by selected check-in date

[Syndicate_Admin]

Hello everyone

I'm trying to calculate a measure in Power BI that shows me the bookings created for a hotel. My goal is that, when you select a specific month in a filter on the page (connected to my Dim_Date date table), a bar chart distributes the bookings created according to their creation date, but only for those bookings whose check-in date belongs to the selected month.

I am currently using this measure:

ReservasCreadas_MesCrea =

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 to select year and month from the Dim_Date date table. However, the measure does not seem to work as I hope. Although the chart updates when you change the filter on the page, the bars are not distributed correctly based on the date of creation (DATECREATION), and it appears that bookings are not being filtered correctly by the date of entry.

I'd like to implement something similar for cancelled bookings, applying the same logic but with the cancellation date instead of the creation date.

Could someone guide me on how to adjust this measure to work as I hope? Any help will be greatly appreciated!

Thank you in advance. 😊

[AmiraBedh]

For your 1st measure :

ReservasCreadas_MesCrea =
CALCULATE(
    DISTINCTCOUNT('Fact_MovRvas'[RESERVA_PK]),
    'Fact_MovRvas'[FECHAENTRADA] IN VALUES(A0Dim_Fecha[Date]),
    REMOVEFILTERS(A0Dim_Fecha)
)

For the canceled measure :

ReservasCanceladas_MesCancela =
CALCULATE(
    DISTINCTCOUNT('Fact_MovRvas'[RESERVA_PK]),
    'Fact_MovRvas'[FECHAENTRADA] IN VALUES(A0Dim_Fecha[Date]),
    REMOVEFILTERS(A0Dim_Fecha)
)

[speedramps · Accepted solution]

You need to use a technique called "role play relationships"

Download my example PBIX solution from Onedrive, and read the info

Click here 

Look at Page1 and Page 2

Adapt it for your creation date, check in date and cancellation date. 

Create measure for eah and then you can mix and match them in filters.

Please click the [acept solution] and thumbs up buttons to show your appreciation, thank you