+----------+------------------+-------+---------+-------------+------------+
| Name | Booking Type | Job | Loading | Start Date | End date |
+----------+------------------+-------+---------+-------------+------------+
| John | Chargeable | job1 | 20 | 04/11/2019 | 31/01/2020 |
| John | Chargeable | job2 | 100 | 01/10/2019 | 31/12/2019 |
| John | Chargeable | job3 | 100 | 14/10/2019 | 01/11/2019 |
| John | Chargeable | job6 | 50 | 29/07/2019 | 30/09/2019 |
| John | Chargeable | job7 | 50 | 29/07/2019 | 30/09/2019 |
| John | Vacation/Holiday | job8 | 100 | 10/01/2020 | 10/02/2020 |
| John | Chargeable | job9 | 100 | 23/03/2020 | 30/07/2020 |
| John | Chargeable | job10 | 50 | 20/01/2020 | 20/03/2020 |
| John | Chargeable | job11 | 20 | 03/02/2020 | 20/03/2020 |
| John | Chargeable | job12 | 30 | 22/01/2020 | 20/03/2020 |
| Kimberly | Chargeable | job11 | 20 | 03/02/2020 | 20/03/2020 |
| Kimberly | Chargeable | job13 | 100 | 22/01/2020 | 20/03/2020 |
| Kimberly | Chargeable | job14 | 100 | 22/04/2020 | 20/04/2020 |
+----------+------------------+-------+---------+-------------+------------+
对于每一行,我试图查找作业日期范围是否与其他作业重叠。如果有重叠,我想对所有重叠作业的加载量求和。
这就是我尝试过的,在表中创建了一个新列:
loading Status =
VAR staffname = 'Staff Booking'[Name ]
VAR jobstart = 'Staff Booking'[Start Date].[Date]
VAR jobend = 'Staff Booking'[End Date].[Date]
VAR bookingtype = 'Staff Booking'[Booking Type ]
RETURN
IF (
CALCULATE (
SUM ( 'Staff Booking'[Loading] ),
FILTER ( 'Staff Booking', 'Staff Booking'[Name ] = staffname ),
FILTER ( 'Staff Booking', 'Staff Booking'[Booking Type ] == "Chargeable" ),
FILTER (
'Staff Booking',
'Staff Booking'[Start Date].[Date] <= jobend
&& jobstart <= 'Staff Booking'[End Date].[Date]
)
) > 100
&& bookingtype = "Chargeable",
"Overbooked",
"Normal"
)
但是,此代码错过了这种特殊情况:
根据上面的DAX公式,深蓝色表示作业超载(> 100),浅蓝色表示作业为正常(100)。
但是,此处的深蓝色(第一和第二)是不正确的,因为它们在任何时间段内都不会超过100。将它们标记为过载的原因是,Dax公式将重叠的所有不同时间段的载荷组合在一起。
例如,工作12 =工作12 +工作10 +工作1 +工作20 = 120
这是错误的,因为作业12在任何时间段始终为100
例如,工作12 =工作12 +工作10 +工作1 = 100
而工作12 =工作12 +工作10 +工作20 = 100
1)有没有办法调整DAX公式或任何其他方法来确保它纠正了上述问题,因此在上述情况下不应将作业12视为超额预定
2)作为纠正上述情况的副作用,有时会发生以下情况:
如果 Job 20
加载设置为50,
那么Job 12的加载总和将看到两个不同的阶段:
阶段1)作业12 =作业12 +作业10 +作业1 = 100(正常)
阶段2)作业12 =作业12(30)+作业10(50)+作业20(50)= 130(超额预定)
在这种情况下,DAX公式应采用最高的相位,并放在“超量预订”列中
如果使用日历表,这会更容易。例如,您可以Name
像这样计算选定对象的负载:
Load =
VAR ThisDate = SELECTEDVALUE ( Dates[Date] )
RETURN
CALCULATE (
SUM ( Booking[Loading] ),
FILTER (
Booking,
Booking[Start Date] <= ThisDate &&
Booking[End date] >= ThisDate &&
Booking[Booking Type] = "Chargeable"
)
)
这是Dates[Date]
x轴和Load
“值”的直观显示。
现在,由于这是分别计算的Dates[Date]
,因此您的加载状态不应像以前那样重叠,并且度量可以很简单:
loading Status = IF ( [Load] > 100, "Overbooked", "Normal" )
编辑:
要将其作为计算列,您需要查看工作期间的每一天,并查看是否[Load]
超过了100
:
LoadingStatus =
VAR TempTable =
ADDCOLUMNS (
FILTER (
Dates,
Dates[Date] <= Booking[End date] &&
Dates[Date] >= Booking[Start Date]
),
"DayLoad", CALCULATE ( [Load], ALLEXCEPT ( Booking, Booking[Name] ) )
)
RETURN
IF ( MAXX ( TempTable, [DayLoad] ) > 100, "Overloaded", "Nomral" )
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句