Consulta DAX (fechas)

[Arnauar97]

Estoy trabajando en un proyecto de Power BI y he encontrado un desafío que no he podido resolver. Necesito ayuda para entender cómo distribuir correctamente las "room nights" (RN) de las reservas en función de las fechas de entrada y salida.

Contexto:

• Tengo un conjunto de datos que incluye información sobre reservas en un hotel, donde cada reserva tiene las siguientes columnas relevantes:
  • FECHAENTRADA: La fecha de entrada de la reserva.
  • FECHASALIDA: La fecha de salida de la reserva.
  • RN OTB: El total de "room nights" asociadas a esa reserva.

    Desafío:

    Quiero crear una medida en DAX que distribuya las "room nights" de cada reserva entre los días que abarca la estancia, de manera que:

    • Si una reserva tiene 6 noches (por ejemplo, del 28 de marzo al 4 de abril), se debería mostrar 1 "room night" en cada uno de esos días:
      • 29 de marzo: 1 RN
      • 30 de marzo: 1 RN
      • 31 de marzo: 1 RN
      • 1 de abril: 1 RN
      • 2 de abril: 1 RN
      • 3 de abril: 1 RN
      • 4 de abril 1 RN

Código Actual:

Aquí está la medida DAX que he estado utilizando, pero solo consigo darle sentido a los datos si ubico el total de RN de cada reserva a la fecha de entrada (el filtro son condiciones extra que queremos hacer en el análisis). 

DAX

RN OTB =

VAR SelectedDate = MAX(A0Dim_Fecha[Date])  // La fecha seleccionada en el reporte

VAR FilteredTable =

    FILTER(

        Fact_ReservasPickup,

        Fact_ReservasPickup[FECHAENTRADA] >= SelectedDate &&  // Solo reservas con entrada futura o igual a la fecha seleccionada

        Fact_ReservasPickup[FECHA_CREA] < SelectedDate &&  // Reservas creadas antes de la fecha seleccionada

        (ISBLANK(Fact_ReservasPickup[FECHA_CANCELACION]) || Fact_ReservasPickup[FECHA_CANCELACION] > SelectedDate)  // Canceladas después de la fecha seleccionada

    )

RETURN

SUMX(

    DISTINCT(SELECTCOLUMNS(FilteredTable, "ID_Reserva", Fact_ReservasPickup[RESERVA_PK])),  // Obtener reservas únicas

    FIRSTNONBLANK(Fact_ReservasPickup[RN OTB], 0)  // Sumar las RN OTB para cada reserva única

)

¿Podrían ayudarme a ajustar esta medida para que reparta las "room nights" correctamente entre las fechas de entrada y salida?

[Poojara_D12]

Hi @Arnauar97 

¡Claro! Aquí tienes la explicación y el código ajustado en español:

Enfoque Actualizado:

1. Calcular los días de estancia: Necesitas calcular el número de días entre la FECHAENTRADA y la FECHASALIDA para cada reserva.
2. Distribuir las noches de habitación: Luego, divides el total de noches de habitación (RN OTB) entre el número de días de la estancia para obtener las noches de habitación por día.

Medida DAX Actualizada

RN_Distribuidas = 
VAR FechaSeleccionada = MAX ( A0Dim_Fecha[Date] )   // La fecha seleccionada en el reporte
VAR TablaFiltrada =
    FILTER (
        Fact_ReservasPickup,
        Fact_ReservasPickup[FECHAENTRADA] <= FechaSeleccionada &&   // Reservas con fecha de entrada en o antes de la fecha seleccionada
        Fact_ReservasPickup[FECHASALIDA] >= FechaSeleccionada &&   // Reservas con fecha de salida en o después de la fecha seleccionada
        ( ISBLANK(Fact_ReservasPickup[FECHA_CANCELACION]) || Fact_ReservasPickup[FECHA_CANCELACION] >= FechaSeleccionada )   // No canceladas antes de la fecha seleccionada
    )
VAR NochesPorDia = 
    ADDCOLUMNS (
        TablaFiltrada,
        "DiasDeEstancia", DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1,  // Calcular los días de estancia, inclusive
        "NochesPorDia", DIVIDE ( Fact_ReservasPickup[RN OTB], DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1 )  // Distribuir RN entre los días
    )
RETURN 
SUMX (
    NochesPorDia,
    [NochesPorDia]   // Sumar las noches de habitación por día para la fecha seleccionada
)

Explicación:

1. FechaSeleccionada: La fecha actualmente seleccionada en tu reporte, que determina qué reservas considerar.
2. TablaFiltrada: Filtra las reservas basándose en la fecha seleccionada. Verifica que la FECHAENTRADA sea en o antes de la fecha seleccionada, la FECHASALIDA sea en o después de la fecha seleccionada, y que la reserva no haya sido cancelada antes de la fecha seleccionada.
3. NochesPorDia: Para cada reserva en TablaFiltrada, calculamos:
   • DiasDeEstancia: El número total de días entre la FECHAENTRADA y la FECHASALIDA, incluyendo ambos días.
   • NochesPorDia: El total de "noches de habitación" dividido entre los días que dura la estancia.
4. SUMX: Suma las "noches por día" para todas las reservas relevantes, distribuyendo efectivamente las noches de habitación entre los días seleccionados.

Ejemplo:

Si una reserva abarca desde el 28 de marzo hasta el 4 de abril con 6 noches de habitación (RN OTB = 6), entonces cada día entre el 29 de marzo y el 4 de abril recibirá 1 noche de habitación.

Pruebas de la Medida:

Puedes probar esta medida en una visualización de tabla con la fecha seleccionada en las filas para ver cómo se distribuyen las noches de habitación a través de los días. Para que funcione correctamente, es recomendable que exista una relación entre tu tabla de fechas (A0Dim_Fecha) y tu tabla de reservas (Fact_ReservasPickup).

Esta medida ahora debería distribuir las noches de habitación correctamente entre cada día del período de estancia para cada reserva. ¡Déjame saber si necesitas más ajustes!

ENGLISH VERSION FOR THE ABOVE ANSWER :

Sure! I understand your goal now: you want to distribute the total "room nights" (RN OTB) across each day between the CHECK-IN DATE and DEPARTURE DATE for every reservation. The current DAX measure you provided is summing the RN OTB based on the selected date, but it doesn’t account for distributing the room nights over the full stay period.

Updated Approach:

1. Calculate the Number of Days in the Stay: You need to calculate the number of days between CHECK-IN DATE and DEPARTURE DATE for each reservation.
2. Distribute the Room Nights: Then, you divide the total room nights (RN OTB) by the number of days in the stay period to get the room nights per day.

Here’s how you can adjust your DAX measure to distribute the "room nights" correctly:

RN_Distributed = 
VAR SelectedDate = MAX ( A0Dim_Fecha[Date] )   // The selected date in the report
VAR FilteredTable =
    FILTER (
        Fact_ReservasPickup,
        Fact_ReservasPickup[FECHAENTRADA] <= SelectedDate &&   // Reservations with check-in on or before the selected date
        Fact_ReservasPickup[FECHASALIDA] >= SelectedDate &&   // Reservations with check-out on or after the selected date
        ( ISBLANK(Fact_ReservasPickup[FECHA_CANCELACION]) || Fact_ReservasPickup[FECHA_CANCELACION] >= SelectedDate )   // Not canceled before the selected date
    )
VAR RoomNightsPerDay = 
    ADDCOLUMNS (
        FilteredTable,
        "DaysInStay", DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1,  // Calculate days of stay, inclusive
        "RoomNightsPerDay", DIVIDE ( Fact_ReservasPickup[RN OTB], DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1 )  // Distribute RN across the days
    )
RETURN 
SUMX (
    RoomNightsPerDay,
    [RoomNightsPerDay]   // Sum the RN per day for the selected date
)

Sure! I understand your goal now: you want to distribute the total "room nights" (RN OTB) across each day between the CHECK-IN DATE and DEPARTURE DATE for every reservation. The current DAX measure you provided is summing the RN OTB based on the selected date, but it doesn’t account for distributing the room nights over the full stay period.

Updated Approach:

1. Calculate the Number of Days in the Stay: You need to calculate the number of days between CHECK-IN DATE and DEPARTURE DATE for each reservation.
2. Distribute the Room Nights: Then, you divide the total room nights (RN OTB) by the number of days in the stay period to get the room nights per day.

Here’s how you can adjust your DAX measure to distribute the "room nights" correctly:

Updated DAX Measure

DAX

Copy code

RN_Distributed = VAR SelectedDate = MAX ( A0Dim_Fecha[Date] ) // The selected date in the report VAR FilteredTable = FILTER ( Fact_ReservasPickup, Fact_ReservasPickup[FECHAENTRADA] <= SelectedDate && // Reservations with check-in on or before the selected date Fact_ReservasPickup[FECHASALIDA] >= SelectedDate && // Reservations with check-out on or after the selected date ( ISBLANK(Fact_ReservasPickup[FECHA_CANCELACION]) || Fact_ReservasPickup[FECHA_CANCELACION] >= SelectedDate ) // Not canceled before the selected date ) VAR RoomNightsPerDay = ADDCOLUMNS ( FilteredTable, "DaysInStay", DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1, // Calculate days of stay, inclusive "RoomNightsPerDay", DIVIDE ( Fact_ReservasPickup[RN OTB], DATEDIFF ( Fact_ReservasPickup[FECHAENTRADA], Fact_ReservasPickup[FECHASALIDA], DAY ) + 1 ) // Distribute RN across the days ) RETURN SUMX ( RoomNightsPerDay, [RoomNightsPerDay] // Sum the RN per day for the selected date )

Explanation:

1. SelectedDate: The date currently selected in your report, which determines which reservations to consider.
2. FilteredTable: Filters reservations based on the selected date. It checks that the reservation's check-in is on or before the selected date, check-out is on or after the selected date, and that the reservation is not canceled before the selected date.
3. RoomNightsPerDay: For each reservation in FilteredTable, we calculate:
   • DaysInStay: The total number of days between the check-in and check-out dates, including both dates.
   • RoomNightsPerDay: The total "room nights" divided by the number of days the reservation spans.
4. SUMX: This sums the "room nights per day" for all the relevant reservations, effectively distributing the room nights across the selected dates.

Example:

If a reservation spans from March 28th to April 4th with 6 room nights (RN OTB = 6), then each day between March 29th and April 4th will be allocated 1 room night.

Testing the Measure:

You can test this measure in a table visual with the selected date on the rows to see how the room nights are distributed across the days. You might want to have a relationship between your date table (A0Dim_Fecha) and your reservation table (Fact_ReservasPickup) for this to work effectively.

This measure should now distribute the room nights properly across each day of the stay period for each reservation. Let me know if you need further adjustments!

¿Respondí a tu pregunta? ¡Marca mi publicación como solución, esto ayudará a otros!

Si mi respuesta(s) te ayudó de alguna manera, no olvides dejarme un "Kudos" 🙂

Saludos cordiales,
Poojara
Analista de Datos | Desarrollador MSBI | Consultor Power BI
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[Anonymous]

Hi @Arnauar97 ,

Based on the description, please create the Date Table.

DateTable = CALENDAR(MIN(Fact_ReservasPickup[FECHAENTRADA]), MAX(Fact_ReservasPickup[FECHA_SALIDA]))

Then, creating a measure to filter RN.

RoomNightsPerDay = 
VAR CheckInDate = Fact_ReservasPickup[FECHAENTRADA]
VAR CheckOutDate = Fact_ReservasPickup[FECHA_SALIDA]
VAR RoomNights = Fact_ReservasPickup[RN OTB]
RETURN
SUMX(
    FILTER(
        DateTable,
        DateTable[Date] >= CheckInDate && DateTable[Date] < CheckOutDate
    ),
    1
)

Besides, create the measure to sum Room Nights.

TotalRoomNights = 
SUMX(
    Fact_ReservasPickup,
    [RoomNightsPerDay]
)

If the calculated measure is incorrect, please provide some sample data.

Best Rergards,

Wisdom Wu

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

[lbendlin]

in DAX you can use CALENDAR and GENERATE.  But it might be simpler to do this in Power Query.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...