当相关日期没有数据(过去和将来)时,如何使用相同的度量进行计算? [英] How to calculate using the same measure when related date has no data (past and future)?
问题描述
尝试了许多方法和公式后,我决定问这个问题。
请参见下面的矩阵图:
WeekDate
是表中的列计划
。它与另一个名为 Export
的表中的另一个日期时间列 Week_Imported
有关。
导出
表仅具有以下日期的值: 19-Dec-19、19-Dec-19和20-Jan-20 而 Planning
表中的日期则跨越了过去和未来的几周。
累积只要表
计划
和导出
就可以正确计算>。
现在,即使没有日期匹配,我也要继续计算。我想从具有数据的 FIRSTDATE
中获取值 32
(在这种情况下为2019年12月23日)和用 32
填充过去。
对于将来的日期,我想使用 LASTDATE
(06年1月6日)的值是 89
。
某事像这样:
WeekDate累积计划计数
。
。
。
19年11月25日32
19年12月2日32
19年12月9日32
19年12月16日32
19年12月23日32 < =包含数据的第一个WeekDate [回填过去32位]
19年12月30日57
19年1月6日89< =包含数据的最后一个WeekDate [填充未来89位]
20-一月20 89
20-一月20 89
20-一月20 89
。
。
。
用于累积 SUM
的公式为
累计计划计数=
计算(
ROUNDUP([1-目标] * MAX(Planning [1-Plan]),0)
,
FILTER(
ALL(Planning [WeekDate]),
Planning [WeekDate]< = MAX( Planning [WeekDate])
)
)
### ####编辑1 #######
在下面使用此度量,我过去得到1s ...
1-目标=
VAR minWeek = MIN(导出[Week_Imported])
VAR targetCount =
计算(
COUNT('Export'[1-Plan]),
FILTER('Export',OR(Export [1-Plan] = YES,Export [1 -计划] = _))))
var minTarget = CALCULATE(
COUNT('Export'[1-Plan]),
FILTER('Export',OR (导出[1-计划] =是,导出[1-计划] = _)
&&导出[Week_Imported] = minWeek))
返回
SWITCH(TRUE,
targetCount = BLANK(),1,//这是我需要获取第一行值(32)和填满上方的列...
targetCount)
问题是无论如何我无法获得2019年12月23日的第一个值(32),无法填写累计计划计数列。
这是我得到的结果使用上面的公式:
WeekDate累积计划计数
。
。
。
-19 19年11月25日1
19年12月2日1
19年12月9日1
19年12月16日1
19年12月23日32 < =包含数据
的第一个WeekDate 19年12月30日57
89年1月6日89< =包含数据
的最后一个WeekDate 20年1月13日89
20年1月20日89
20年1月20日89 b
$ b。
。
#######编辑2 #######
我整理了一个简化的Sample.pbix,它显示了我正在尝试
并用最少的数据进行测试东西:
以该度量为起点,我们需要将计算范围扩展到不存在数据的时期。为此,我们需要处理评估
[计划计数]
的过滤器上下文。
我们需要获取存在
导出
数据的第一周和最后一周。这是一个返回数据的第一个日期的公式,与切片器无关。第一个数据周=
计算(
分钟(Planning [WeekDate]),
REMOVEFILTERS(Planning [WeekDate]),
TREATAS(
CALCULATETABLE(
VALUES('Export'[Week_Imported]) ,-外键引用Planning [WeekDate]
REMOVEFILTERS(Planning)
),
Planning [WeekDate]
)
)
After trying a lot of approaches and formulas I decided to ask this question.
See this Matrix visual:
WeekDate
is column in a table calledPlanning
. It's related to another date time columnWeek_Imported
in another table calledExport
.
Export
table only has values for the dates: 23-Dec-19, 30-Dec-19 and 06-Jan-20 whereasPlanning
table has dates spanning multiple weeks in the past and future.
Cumulative Plan Count
is calculating correctly as long as there are matching dates between the tablesPlanning
andExport
.Now I'd like to keep calculating even when there's no date matching. I want to get the value
32
from theFIRSTDATE
which has data (in this case 23-Dec-2019) and backfill the past with32
.For dates in the future I'd like to use
LASTDATE
(06-Jan-20) value which is89
.Something like this:
WeekDate Cumulative Plan Count . . . 25-Nov-19 32 02-Dec-19 32 09-Dec-19 32 16-Dec-19 32 23-Dec-19 32 <= First WeekDate which has data [backfill past with 32] 30-Dec-19 57 06-Jan-19 89 <= Last WeekDate which has data [fill future with 89] 13-Jan-20 89 20-Jan-20 89 27-Jan-20 89 . . .
The formula used for the cumulative
SUM
is this:Cumulative Plan Count = CALCULATE ( ROUNDUP([1 - Target] * MAX(Planning[1 - Plan]), 0) , FILTER ( ALL ( Planning[WeekDate] ), Planning[WeekDate] <= MAX(Planning[WeekDate]) ) )
####### Edit 1 #######
Using this measure below I get 1s for the past...
1 - Target = VAR minWeek = MIN(Export[Week_Imported]) VAR targetCount = CALCULATE ( COUNT( 'Export'[1 - Plan]), FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_"))) var minTarget = CALCULATE ( COUNT( 'Export'[1 - Plan]), FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_") && Export[Week_Imported] = minWeek)) RETURN SWITCH(TRUE, targetCount = BLANK(), 1, // Here is where I need to get the 1st row value (32) and fill up the column up above... targetCount)
The problem is that no matter what I do I can't get the 1st value for 23-Dec-2019 (32) to fill up the Cumulative Plan Count column.
This is the result when I use the formula above:
WeekDate Cumulative Plan Count . . . 25-Nov-19 1 02-Dec-19 1 09-Dec-19 1 16-Dec-19 1 23-Dec-19 32 <= First WeekDate which has data 30-Dec-19 57 06-Jan-19 89 <= Last WeekDate which has data 13-Jan-20 89 20-Jan-20 89 27-Jan-20 89 . . .
####### Edit 2 #######
I put together a simplified Sample.pbix which shows what I'm trying to accomplish with minimum data to test things: https://drive.google.com/drive/folders/1zxS_2VE9_0JEMXvsg9Dq196BK552RbNo?usp=sharing
This screenshot has more details: https://drive.google.com/open?id=1_-IMEpLwuWWN6vrrT_TNWbeqZ7f1LOan
解决方案Let me introduce the solution with intermediate steps.
In your data schema,
Planning
andExport
tables are in one to many relationship.Planning
is in the grain of every week, whileExport
has more rows for each week.On this basis, the measure to count the number of
Export
rows for eachPlanning
week is as simple as this.Plan Count (Basic) = COUNTROWS ( 'Export' )
When you slice by
Planning[WeekDate]
, this measure returns the counts ofExport
rows for the corresponding weeks.Actually, you need
FILTER
to count only rows you are interested in.Plan Count = COUNTROWS ( FILTER ( 'Export', OR ( 'Export'[Plan] = "YES", 'Export'[Plan] = "_" ) ) )
Here is the result we get so far.
Having this measure as the starting point, we need to extend the calculation to the periods where the data does not exist. In order to do that, we need to handle the filter context where
[Plan Count]
is evaluated.We need to get the first and final weeks where
Export
data exists. Here is a formula that returns the first date of the data regardless of the slicer.First Data Week = CALCULATE ( MIN ( Planning[WeekDate] ), REMOVEFILTERS ( Planning[WeekDate] ), TREATAS ( CALCULATETABLE ( VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate] REMOVEFILTERS ( Planning ) ), Planning[WeekDate] ) )
We can use this date to modify the filter context to calculate
[Plan Count]
of the first data week. In fact, below measure always returns 1064, which is the number of[Plan Count]
in December 30, 2019.First Week Plan Count = VAR _FirstDataWeek = CALCULATE ( MIN ( Planning[WeekDate] ), REMOVEFILTERS ( Planning[WeekDate] ), TREATAS ( CALCULATETABLE ( VALUES ( 'Export'[Week_Imported] ), REMOVEFILTERS ( Planning ) ), Planning[WeekDate] ) ) RETURN CALCULATE ( [Plan Count], Planning[WeekDate] = _FirstDataWeek )
Using this technique, we can expand the first and final values of
[Plan Count]
to the past and the future dates. Below is the final formula, which iterates overPlanning
table, and apply different filter context to calculate[Plan Count]
.Extended Plan Count = -- Planning[WeekDate] values where Export data exists VAR _DataWeeks = CALCULATETABLE ( VALUES ( Planning[WeekDate] ), REMOVEFILTERS ( Planning[WeekDate] ), TREATAS ( CALCULATETABLE ( VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate] REMOVEFILTERS ( Planning ) ), Planning[WeekDate] ) ) -- First and last Planning[WeekDate] where Export data exists VAR _FirstDataWeek = MINX ( _DataWeeks, [WeekDate] ) VAR _FinalDataWeek = MAXX ( _DataWeeks, [WeekDate] ) -- [Plan Count] values of first and last weeks VAR _FirstDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FirstDataWeek ) VAR _FinalDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FinalDataWeek ) RETURN SUMX ( Planning, SWITCH ( TRUE, Planning[WeekDate] < _FirstDataWeek, _FirstDataWeekPlanCount, Planning[WeekDate] > _FinalDataWeek, _FinalDataWeekPlanCount, [Plan Count] ) )
这篇关于当相关日期没有数据(过去和将来)时,如何使用相同的度量进行计算?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!