创建DAX以计算Power BI中每个日历日期的已占用房间数 [英] Create DAX to count number of occupied rooms per calendar date in Power BI

问题描述

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

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

因此，如果说有6个储备金，其中 DateIN和 DateOUT不同我将能够计算出每天有多少房间。我已经通过使用列公式完成了此操作-但我需要将其作为一种措施，以便通过切片器进行过滤。

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）

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]
    )
)

只需使用 MAX（Calendario [Fecha]）获取上下文中的当前日期，您可以使用它与 F_Reservas 表中的日期范围进行比较。

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.

请注意 3/1 /的计算在您的示例中，2017 是 4 ，而不是 3 。同样， 2017/7/1 / 没有预订，因此它返回 Blank ，这就是它没有出现的原因。

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.

这篇关于创建DAX以计算Power BI中每个日历日期的已占用房间数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！