创建 DAX 以计算 Power BI 中每个日历日期的占用房间数

本文介绍了创建 DAX 以计算 Power BI 中每个日历日期的占用房间数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试计算每个日历日期的已占用房间"数量.

因此，如果有 6 个具有不同DateIN"和DateOUT"的保留区，我将能够计算出每天有多少房间被占用.我已经通过使用列公式来做到这一点 - 但我需要它作为一个度量，以便被切片器过滤.

这是我需要的一个简单示例:

BOOKINGID DateIN DateOUT000001 01-01-2017 06-01-2017000002 01-01-2017 03-01-2017000003 02-01-2017 03-01-2017000004 03-01-2017 05-01-2017000005 08-01-2017 10-01-2017

入住日期01-01-2017 202-01-2017 303-01-2017 304-01-2017 205-01-2017 206-01-2017 107-01-2017 008-01-2017 109-01-2017 110-01-2017 1

这是我用来作为列执行此操作的公式:

OcupacionHL = CALCULATE(count(F_Reservas[Reservas]); FILTER(F_Reservas;(F_Reservas[Fecha Inicio] <= D_Calendario[FECHA]) &&(F_Reservas[FechaFin] >= D_Calendario[FECHA]));F_Reservas[Hotel] = "酒店 1")

这是我的数据模型的图像:

解决方案

该计算的度量版本与您尝试的没有太大区别.

OcupacionHL =计算 (DISTINCTCOUNT ( F_Reservas[BookingID] ),筛选 (全部(F_Reservas)，MAX ( Calendario[Fecha] ) >= [DateIN]&&MAX ( Calendario[Fecha] ) <= [DateOUT]))

只需使用 MAX(Calendario[Fecha]) 在上下文中获取当前日期，您可以使用它与 F_Reservas 表中的日期范围进行比较.p>

注意 3/1/2017 的计算是 4 而不是 3 在您的示例中.另外 7/1/2017 没有预订，因此它返回 BLANK 这就是它没有出现的原因.

I am trying to count the number of "Occupied rooms" per calendar date.

so if there was say 6 reserves with diffrent "DateIN" and "DateOUT" I would be able to calculate how many rooms occupied there was every day. I have done this by using a column formula - but I need it to be a measure in order to be filtered by slicers.

Here is a simple example of what I would need:

BOOKINGID DateIN DateOUT 000001 01-01-2017 06-01-2017 000002 01-01-2017 03-01-2017 000003 02-01-2017 03-01-2017 000004 03-01-2017 05-01-2017 000005 08-01-2017 10-01-2017

DATE OCCUPIEDROOMS 01-01-2017 2 02-01-2017 3 03-01-2017 3 04-01-2017 2 05-01-2017 2 06-01-2017 1 07-01-2017 0 08-01-2017 1 09-01-2017 1 10-01-2017 1

here is a the formula i used to do this as a column:

OcupacionHL = CALCULATE(count(F_Reservas[Reservas]); FILTER(F_Reservas; (F_Reservas[Fecha Inicio] <= D_Calendario[FECHA]) && (F_Reservas[Fecha Fin] >= D_Calendario[FECHA])); F_Reservas[Hotel] = "Hotel 1")

And here is an image of my data model:

解决方案

The measure version of that calculation is not much different from what you tried.

OcupacionHL = CALCULATE ( DISTINCTCOUNT ( F_Reservas[BookingID] ), FILTER ( ALL ( F_Reservas ), MAX ( Calendario[Fecha] ) >= [DateIN] && MAX ( Calendario[Fecha] ) <= [DateOUT] ) )

Just use MAX(Calendario[Fecha]) to get the current date in context and you can use it to compare against the date range in F_Reservas table.

Note the calculation for 3/1/2017 is 4 instead of 3 in your example. Also 7/1/2017 has no bookings so it returns BLANK that's the reason it doesn't appear.